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 --------------------------------------------------------------------- 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
