List:General Discussion« Previous MessageNext Message »
From:Erv Young Date:June 10 2002 2:18am
Subject:Re: Not using indexes???
View as plain text  
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

Thread
Maximum JOINED tablesArthur Fuller5 Jun
  • Re: Maximum JOINED tablesRoger Baklund5 Jun
RE: Maximum JOINED tablesWalt Weaver5 Jun
  • RE: Maximum JOINED tablesAndrew Hazen5 Jun
RE: Maximum JOINED tablesWalt Weaver5 Jun
RE: Maximum JOINED tablesWalt Weaver5 Jun
  • Re: Maximum JOINED tablesSabine Richter5 Jun
    • Re: Maximum JOINED tablesDan Nelson5 Jun
  • Re: Maximum JOINED tablesRoger Baklund5 Jun
    • Re: Maximum JOINED tablesHarald Fuchs6 Jun
      • RE: Maximum JOINED tablesRoger Baklund6 Jun
        • Re: RE: Maximum JOINED tablesVictoria Reznichenko6 Jun
          • Re: Maximum JOINED tablesRoger Baklund6 Jun
            • Re: Maximum JOINED tablesBenjamin Pflugmann7 Jun
              • Re: Maximum JOINED tablesRoger Baklund7 Jun
                • Re: Re: Maximum JOINED tablesVictoria Reznichenko7 Jun
                  • Re: Maximum JOINED tablesRoger Baklund7 Jun
                • Re: Maximum JOINED tablesBenjamin Pflugmann7 Jun
                  • Re: Maximum JOINED tablesRoger Baklund8 Jun
                    • Not using indexes???Jon Frisby8 Jun
                      • Re: Not using indexes???Erv Young8 Jun
                      • Re: Not using indexes???bvyas38 Jun
                        • RE: Not using indexes???Jon Frisby8 Jun
                          • Re: Not using indexes???Benjamin Pflugmann8 Jun
                            • Re: Not using indexes???Erv Young10 Jun
                            • RE: Not using indexes???Jon Frisby10 Jun
                              • php+mysql+multiple lines qrycristian ditoiu10 Jun
                                • Re: php+mysql+multiple lines qryBrent Baisley10 Jun
                                  • Re: php+mysql+multiple lines qrycristian ditoiu10 Jun