At 03:11 PM 6/8/2002 +0200, Benjamin Pflugmann wrote:
>Hi.
>
>As far as I can see is that you use a condition in your WHERE clause
>which MySQL will not (yet?) use indexes for. See
>
> http://www.mysql.com/doc/M/y/MySQL_indexes.html
> (seems to be mainly about MyISAM tables)
>
>to see how indexes are used. You use "IS NOT NULL". This page states
>nowhere that this will be able to make use of an index (only "IS NULL"
>does).
>
>E.g. the non-equivalence operator is the same. MySQL will use indexes
>for "foo>0", but not "foo<>0", which ask for the same result (presumed
>foo is an unsigned column).
Yes, but the manual also does not say that it does _not_ use an index for
negated operators. It does not mention the inequality comparison operator
<>. It does not specifically mention IS NOT NULL.
...
>But type=range does mean that MySQL is using an index.
>
>Just in case that this is not clear: "using index" in the "Extra" does
>not indicate whether an index can be used (a non-NULL value in "key"
>does), but that the index alone is sufficent and the data file does
>not has to be touched.
If we take this observation as impetus to actually take the SELECT
statements and the EXPLAIN output from Jon's original message, and piece
the broken lines back together (supplying elided spaces as needed), and
then do the necessary RTFM at
http://www.mysql.com/doc/E/X/EXPLAIN.html
it would appear that Jon is mistaken. Three of his four queries actually
do use the sequence_log_id index, according to the output he
presented. Only the second query does not use the index.
This knowledge is hardly reassuring; it doesn't make the queries run any
faster. In fact, the EXPLAIN output suggests that the only purpose for
which the index has been used is to determine the order in which MySQL
visits each one of the ~1M rows in the table, and not to limit its
attention to just those few rows of interest.
To rephrase Jon's original question, what can be done to cause MySQL to
process these queries quickly? I don't think we have answered this yet.
--Erv
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
