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 - 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: 

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.