MySQL 3.23.44 isn't using indexes when it should and could, even when I
try to force it via a "USE INDEX" clause in the SELECT statement. For
example, here is what EXPLAIN gives me on a query containing the clause
"USE INDEX (bug_status)" on the bugs table:
mysql> EXPLAIN SELECT DISTINCT bugs.bug_id, bugs.groupset,
substring(bugs.bug_severity, 1, 3), substring(bugs.priority, 1, 3),
substring(bugs.rep_platform, 1, 3), map_assigned_to.login_name,
substring(bugs.bug_status,1,4), substring(bugs.resolution,1,4),
substring(bugs.short_desc, 1, 60) FROM bugs USE INDEX (bug_status),
profiles map_assigned_to, profiles map_reporter LEFT JOIN profiles
map_qa_contact ON bugs.qa_contact = map_qa_contact.userid WHERE
((bugs.groupset & 0) = bugs.groupset ) AND bugs.assigned_to =
map_assigned_to.userid AND bugs.reporter = map_reporter.userid AND
(bugs.bug_status = 'NEW' OR bugs.bug_status = 'ASSIGNED' OR
bugs.bug_status = 'REOPENED') ORDER BY bugs.priority, bugs.bug_severity;
+-----------------+--------+---------------------------------+---------+---------+------------------+--------+---------------------------------------------+
| table | type | possible_keys | key |
key_len | ref | rows |
Extra |
+-----------------+--------+---------------------------------+---------+---------+------------------+--------+---------------------------------------------+
| bugs | ALL | assigned_to,bug_status,reporter | NULL
| NULL | NULL | 139031 | where used; Using temporary;
Using filesort |
| map_assigned_to | eq_ref | PRIMARY | PRIMARY
| 3 | bugs.assigned_to | 1
| |
| map_reporter | eq_ref | PRIMARY | PRIMARY
| 3 | bugs.reporter | 1 | Using index;
Distinct |
| map_qa_contact | eq_ref | PRIMARY | PRIMARY
| 3 | bugs.qa_contact | 1 | Using index;
Distinct |
+-----------------+--------+---------------------------------+---------+---------+------------------+--------+---------------------------------------------+
4 rows in set (0.09 sec)
On MySQL 3.23.41, however, MySQL uses the index I tell it to use (and
also uses it without me having to tell it):
mysql> EXPLAIN SELECT DISTINCT bugs.bug_id, bugs.groupset,
substring(bugs.bug_severity, 1, 3), substring(bugs.priority, 1, 3),
substring(bugs.rep_platform, 1, 3), map_assigned_to.login_name,
substring(bugs.bug_status,1,4), substring(bugs.resolution,1,4),
substring(bugs.short_desc, 1, 60) FROM bugs USE INDEX (bug_status),
profiles map_assigned_to, profiles map_reporter LEFT JOIN profiles
map_qa_contact ON bugs.qa_contact = map_qa_contact.userid WHERE
((bugs.groupset & 0) = bugs.groupset ) AND bugs.assigned_to =
map_assigned_to.userid AND bugs.reporter = map_reporter.userid AND
(bugs.bug_status = 'NEW' OR bugs.bug_status = 'ASSIGNED' OR
bugs.bug_status = 'REOPENED') ORDER BY bugs.priority, bugs.bug_severity;
+-----------------+--------+---------------------------------+------------+---------+------------------+-------+---------------------------------------------+
| table | type | possible_keys |
key | key_len | ref | rows |
Extra |
+-----------------+--------+---------------------------------+------------+---------+------------------+-------+---------------------------------------------+
| bugs | range | assigned_to,bug_status,reporter |
bug_status | 1 | NULL | 22665 | where used; Using
temporary; Using filesort |
| map_assigned_to | eq_ref | PRIMARY |
PRIMARY | 3 | bugs.assigned_to | 1
| |
| map_reporter | eq_ref | PRIMARY |
PRIMARY | 3 | bugs.reporter | 1 | Using index;
Distinct |
| map_qa_contact | eq_ref | PRIMARY |
PRIMARY | 3 | bugs.qa_contact | 1 | Using index;
Distinct |
+-----------------+--------+---------------------------------+------------+---------+------------------+-------+---------------------------------------------+
4 rows in set (0.01 sec)
Is this a bug in MySQL, a problem with how I migrated the data, or some
other issue? How can I fix it?
-myk
