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
