List:General Discussion« Previous MessageNext Message »
From:Jon Frisby Date:June 8 2002 1:07am
Subject:Not using indexes???
View as plain text  
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

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