MySQL not use indexes for subqueries used in IN clause

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 -

Patch for this issue was posted on and hopefuly with next modules update I will not need to patch it again -


Add new comment

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.

To submit this form you need to use Internet Explorer v10+ or recent version of Chrome, Firefox or Safari.