mysql - Why does a query with an IN (subquery) take longer than a query with IN (discrete list) -

this has bugged me, why query

select    *    `table`  `value` in    (select      val        other_table    `date` < '2014-01-01') 

run orders of magnitude slower sequentially running both query

select    `val`    other_table  `date` < '2014-01-01'   result: +----+ | val | +-----+ | v1  | | v2  | | v3  | | v7  | | v12 | +-----+ 

and query:

select    *    `table`  `value` in ('v1', 'v2', 'v3', 'v7', 'v12') 

from docs: (emphasis added me)

subquery optimization in not effective = operator or in(value_list) operator.

a typical case poor in subquery performance when subquery returns small number of rows outer query returns large number of rows compared subquery result.

the problem that, statement uses in subquery, optimizer rewrites correlated subquery. consider following statement uses uncorrelated subquery:

select ... t1 t1.a in (select b t2);

the optimizer rewrites statement correlated subquery:

select ... t1 exists (select 1 t2 t2.b = t1.a);

if inner , outer queries return m , n rows, respectively, execution time becomes on order of o(m×n), rather o(m+n) uncorrelated subquery.

an implication in subquery can slower query written using in(value_list) operator lists same values subquery return.

http://dev.mysql.com/doc/refman/5.7/en/subquery-restrictions.html

hopes helps else might have been curious