CODEWITHSUNDEEP
mysqlquery-optimization
JS_Riddler
JS_Riddler

Reputation: 1580

Is MySQL able to optimize: where `indexed_field` IN (<subquery>)?

Why isn't MySQL able to consistently optimize queries in the format of WHERE <indexed_field> IN (<subquery>)?

I have a query as follows:

SELECT
    * 
FROM
    t1
WHERE
    t1.indexed_field IN (select val from ...)
    AND (...)

The subquery select val from ... runs very quickly. The problem is MySQL is doing a full table scan to get the required rows from t1 -- even though t1.indexed_field is indexed.

I've gotten around this by changing the query to an inner join:

SELECT
    * 
FROM
    t1
INNER JOIN
    (select val from ...) vals ON (vals.val = t1.indexed_field)
WHERE
    (...)

Explain shows that this works perfectly -- MySQL is now able use the indexed_field index when joining to the subquery table.

My question is: Why isn't MySQL able to optimize the first query? Intuitively, doing where <indexed_field> IN (<subquery>) seems like quite an easy optimization -- do the subquery, use the index to grab the rows.

Upvotes: 0

Views: 26

Answers (1)

Rick James
Rick James

Reputation: 142296

No or Yes.

Old versions of MySQL did a very poor job of optimizing IN ( SELECT ... ). It seemed to re-evaluate the subquery repeatedly.

New versions are turning it into EXISTS ( SELECT 1 ... ) or perhaps a LEFT JOIN.

Please provide

  • Version
  • SHOW CREATE TABLE
  • EXPLAIN SELECT ...

Upvotes: 1

Related Questions