List:General Discussion« Previous MessageNext Message »
From:Erv Young Date:June 8 2002 1:55am
Subject:Re: Not using indexes???
View as plain text  
Jon,

Have you tried phrasing your WHERE clause as
         WHERE sequence_log_id >= 0 ?
If there is a determinate lower bound other than zero, then substitute it 
for 0 as the second comparand.

Let us know how it comes out.

(And however it comes out, don't ask me why.  The real experts can sort 
that out for both of us when they get to their desks, in about 4 hours' time.)

--Erv

At 06:07 PM 6/7/2002 -0700, Jon Frisby wrote:
>Please excuse me if this is something blindingly obvious, but having now
>encountered this in several circumstances, but I have been unable to find a
>resolution in the docs (perhaps I just missed it?).
>
>In actuality we encountered the problem when doing some nasty joins, but the
>problem seems really to be more fundamental as we've reproduced the problem
>without a join...
>
>We're using MySQL 4.0.1 on Linux (from the RPM).  We have an InnoDB table,
>'click' with 6 or so indexes on it.  Notably:
>-`id` is the PRIMARY KEY.
>-`sequence_log_id` has a non-unique index.
>
>The problem is when we do queries that involve columns *not* available
>directly from the index, MySQL absolutely refuses to use an index for
>looking up rows.  See the output from EXPLAIN for each of these queries:
>
>SELECT COUNT(*) FROM click WHERE sequence_log_id IS NOT NULL;
>+-------+-------+-----------------+-----------------+---------+------+------
>--+-------------------------+
>| table | type  | possible_keys   | key             | key_len | ref  | rows
>| Extra                   |
>+-------+-------+-----------------+-----------------+---------+------+------
>--+-------------------------+
>| click | index | sequence_log_id | sequence_log_id |       5 | NULL |
>939470 | where used; Using index |
>+-------+-------+-----------------+-----------------+---------+------+------
>--+-------------------------+
>
>
>SELECT COUNT(*), SUM(actual_revenue) FROM click WHERE sequence_log_id IS NOT
>NULL;
>+-------+------+-----------------+------+---------+------+--------+---------
>---+
>| table | type | possible_keys   | key  | key_len | ref  | rows   | Extra
>|
>+-------+------+-----------------+------+---------+------+--------+---------
>---+
>| click | ALL  | sequence_log_id | NULL |    NULL | NULL | 939470 | where
>used |
>+-------+------+-----------------+------+---------+------+--------+---------
>---+
>(`actual_revenue` isn't part of the index.
>
>
>SELECT COUNT(*), SUM(id) FROM click WHERE sequence_log_id IS NOT NULL;
>+-------+-------+-----------------+-----------------+---------+------+------
>--+-------------------------+
>| table | type  | possible_keys   | key             | key_len | ref  | rows
>| Extra                   |
>+-------+-------+-----------------+-----------------+---------+------+------
>--+-------------------------+
>| click | index | sequence_log_id | sequence_log_id |       5 | NULL |
>939470 | where used; Using index |
>+-------+-------+-----------------+-----------------+---------+------+------
>--+-------------------------+
>(Since it's an InnoDB table, a secondary index uses the PK as a reference
>for the index.)
>
>
>SELECT COUNT(*), SUM(actual_revenue) FROM click WHERE sequence_log_id IS NOT
>NULL GROUP BY sequence_log_id;
>+-------+-------+-----------------+-----------------+---------+------+------
>--+------------+
>| table | type  | possible_keys   | key             | key_len | ref  | rows
>| Extra      |
>+-------+-------+-----------------+-----------------+---------+------+------
>--+------------+
>| click | index | sequence_log_id | sequence_log_id |       5 | NULL |
>939469 | where used |
>+-------+-------+-----------------+-----------------+---------+------+------
>--+------------+
>(The key isn't actually *used* here, as demonstrated by the query taking a
>long time to run...  Of the ~970k rows in click, about 2800 have
>sequence_log_id IS NOT NULL.)
>
>
>
>My question is:  Is this a bug?  A feature?  What can I do to get MySQL to
>use the sequence_log_id index?
>
>-JF
>
>
>---------------------------------------------------------------------
>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 <mysql-thread111373@stripped>
>To unsubscribe, e-mail <mysql-unsubscribe-res04ft9=gte.net@stripped>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

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