I have some more information about the problem. It seems to be caused
by the presence of an OR or IN clause in the WHERE portion of the query.
Without the clause, 2.23.44 uses the appropriate indexes, but with the
clause it does not. 2.23.41 uses the appropriate indexes in both cases.
Here are some simple queries that demonstrate this:
2.23.44:
mysql> EXPLAIN SELECT DISTINCT bugs.bug_id FROM bugs USE INDEX
(bug_status) WHERE bugs.bug_status = 'NEW';
+-------+------+---------------+------------+---------+-------+-------+-----------------------------+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+-------+------+---------------+------------+---------+-------+-------+-----------------------------+
| bugs | ref | bug_status | bug_status | 1 | const | 18269 |
where used; Using temporary |
+-------+------+---------------+------------+---------+-------+-------+-----------------------------+
1 row in set (0.03 sec)
mysql> EXPLAIN SELECT DISTINCT bugs.bug_id FROM bugs USE INDEX
(bug_status) WHERE bugs.bug_status IN ('NEW', 'ASSIGNED', 'REOPENED');
+-------+------+---------------+------+---------+------+--------+-----------------------------+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+-------+------+---------------+------+---------+------+--------+-----------------------------+
| bugs | ALL | bug_status | NULL | NULL | NULL | 139031 | where
used; Using temporary |
+-------+------+---------------+------+---------+------+--------+-----------------------------+
1 row in set (0.06 sec)
2.23.41:
mysql> EXPLAIN SELECT DISTINCT bugs.bug_id FROM bugs USE INDEX
(bug_status) WHERE bugs.bug_status = 'NEW';
+-------+------+---------------+------------+---------+-------+-------+-----------------------------+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+-------+------+---------------+------------+---------+-------+-------+-----------------------------+
| bugs | ref | bug_status | bug_status | 1 | const | 13904 |
where used; Using temporary |
+-------+------+---------------+------------+---------+-------+-------+-----------------------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT DISTINCT bugs.bug_id FROM bugs USE INDEX
(bug_status) WHERE bugs.bug_status IN ('NEW', 'ASSIGNED', 'REOPENED');
+-------+-------+---------------+------------+---------+------+-------+-----------------------------+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+-------+-------+---------------+------------+---------+------+-------+-----------------------------+
| bugs | range | bug_status | bug_status | 1 | NULL | 22665 |
where used; Using temporary |
+-------+-------+---------------+------------+---------+------+-------+-----------------------------+
1 row in set (0.00 sec)
---------------------------------------------------------------------
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
