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 <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
