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
