MySQL not use indexes for subqueries used in IN clause

Submitted by Pavel Karoukin on Wed, 01/23/2013 - 00:00

One of clients mentioned awful slow query from Drupal's Boost module:

DELETE FROM boost_crawler WHERE hash IN (
  SELECT ca.hash_url FROM boost_cache ca
  WHERE ca.expire BETWEEN 1 AND 434966399 OR ca.expire > 434966400
)

Running EXPLAIN query turns out MySQL ignores indexes for boost_crawler.hash column completely. And I am not alone:

As a result this query blocks any inserts into boost_cache table and essentially any incoming request. Which is very bad. Apparently, this bug supposed to be fixed in MySQL 6.x, but it is not production ready I believe. MySQL documentation advises to use EXISTS statement - http://dev.mysql.com/doc/refman/5.1/en/subquery-optimization-with-exists...

Patch for this issue was posted on drupal.org and hopefuly with next modules update I will not need to patch it again - http://drupal.org/node/1897268

Tags: