Steven Roussey wrote: >The corollary to the above quote is that MySQL can not use indexes with >an OR clause at the base level. > MySQL *does* use the index on a different server, though, it just doesn't use the index on that server. Compare the results of EXPLAIN queries on the two servers (after ANALYZE TABLE was run):
Broken server: mysql> EXPLAIN SELECT bug_id FROM bugs WHERE bug_status IN ('NEW', 'ASSIGNED', 'REOPENED'); +-------+------+---------------+------+---------+------+--------+------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+------+---------------+------+---------+------+--------+------------+ | bugs | ALL | bug_status | NULL | NULL | NULL | 139425 | where used | +-------+------+---------------+------+---------+------+--------+------------+ Working server: mysql> EXPLAIN SELECT bug_id FROM bugs WHERE bug_status IN ('NEW', 'ASSIGNED', 'REOPENED'); +-------+-------+---------------+------------+---------+------+-------+------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+-------+---------------+------------+---------+------+-------+------------+ | bugs | range | bug_status | bug_status | 1 | NULL | 20502 | where used | +-------+-------+---------------+------------+---------+------+-------+------------+ My initial hunch was a bug in 3.23.44 (the working server was running 3.23.41 and the broken server was running 3.23.44)), but that doesn't seem to be the case, since I upgraded the working server to 3.23.44 and found that it continues to correctly use the index for this query. Other possibilities include platform (the working server is Redhat Linux 7.2 while the broken server is SunOS 5.7) and configuration, but I'm not sure how to diagnose further or what to look for in the configuration files. Note that although this particular query is trivial, it is a base for more complex queries with one-to-many joins selecting large numbers of records, and narrowing the range of records to join can significantly increase query performance in those situations. -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