Sometimes MySQL doesn’t use the index on a column when performing a query.

Indexes are NOT used if MySQL can calculate that it will probably be faster to scan the whole table. For example if key_part1 is evenly distributed between 1 and 100, it’s not good to use an index in the following query:

• SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90

So what do you do in that case? This was my query:

SELECT dispId, outcome, contacted, householder, time_stamp
FROM `dispositions`
WHERE `leadId` = 1 OR (`leadId` IS NULL AND `campaignId` = 8 AND `xid` = 'AAA100000000148')
ORDER BY `dispId` DESC
LIMIT 1

In this table (~175,000 rows), leadId, campaignId, and xid all have indexes, but MySQL was doing a table scan (~2.2 sec) because of the duplicate use of leadId and the way the WHERE clause was structured. This rather unusual optimization was more than 10x faster (~.17 sec):

SELECT dispId, outcome, contacted, householder, time_stamp
FROM `dispositions`
WHERE `campaignId` = 8 AND `xid` = 'AAA100000000148' AND `leadId` IS NULL

UNION ALL

SELECT dispId, outcome, contacted, householder, time_stamp
FROM `dispositions`
WHERE `leadId` = 1

ORDER BY `dispId` DESC
LIMIT 1

An odd case where two queries are faster than one!