MySQL Bugs: #36660: SUBQUERY slow

Bug #36660	SUBQUERY slow
Submitted:	12 May 2008 4:45	Modified:	21 Mar 2011 4:14
Reporter:	Roberto Spadim (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.1.20	OS:	Linux
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any
Tags:	qc

Description:
using 
key IN (select)
is slower than

from table as t1, (select) as t2 where t1.key=t2.key

see howto reproduce

How to repeat:
fast:

explain
SELECT moeda,valor_face,doc_hash_key FROM dig_documentos as c, (SELECT hash_origem as doc FROM dig_contacorrente WHERE lote_unidade=1001 AND lote_data="2008-05-12" AND lote_numero=3 
UNION 
SELECT hash_origem FROM mov_contacorrente WHERE lote_unidade=1001 AND lote_data="2008-05-12" AND lote_numero=3 
UNION 
SELECT cc_hash_baixa FROM dig_baixas WHERE lote_unidade=1001 AND lote_data="2008-05-12" AND lote_numero=3 
UNION 
SELECT cc_hash_baixa FROM mov_baixas WHERE lote_unidade=1001 AND lote_data="2008-05-12" AND lote_numero=3)  as d
WHERE c.doc_hash_key=d.doc
UNION 

SELECT moeda,valor_face,doc_hash_key FROM mov_documentos AS a,(
SELECT SQL_SMALL_RESULT  hash_origem as doc FROM dig_contacorrente WHERE lote_unidade=1001 AND lote_data="2008-05-12" AND lote_numero=3 
UNION  all
SELECT hash_origem FROM mov_contacorrente WHERE lote_unidade=1001 AND lote_data="2008-05-12" AND lote_numero=3 
UNION all
SELECT cc_hash_baixa FROM dig_baixas WHERE lote_unidade=1001 AND lote_data="2008-05-12" AND lote_numero=3 
UNION all
SELECT cc_hash_baixa FROM mov_baixas WHERE lote_unidade=1001 AND lote_data="2008-05-12" AND lote_numero=3
)  as b 
WHERE 
a.doc_hash_key =b.doc 
GROUP BY doc_hash_key

slow:

explain
SELECT moeda,valor_face,doc_hash_key FROM dig_documentos WHERE doc_hash_key IN (SELECT hash_origem FROM dig_contacorrente WHERE lote_unidade=1001 AND lote_data="2008-05-12" AND lote_numero=3 
UNION 
SELECT hash_origem FROM mov_contacorrente WHERE lote_unidade=1001 AND lote_data="2008-05-12" AND lote_numero=3 
UNION 
SELECT cc_hash_baixa FROM dig_baixas WHERE lote_unidade=1001 AND lote_data="2008-05-12" AND lote_numero=3 
UNION 
SELECT cc_hash_baixa FROM mov_baixas WHERE lote_unidade=1001 AND lote_data="2008-05-12" AND lote_numero=3) 
UNION 
SELECT moeda,valor_face,doc_hash_key FROM mov_documentos WHERE doc_hash_key IN (SELECT hash_origem FROM dig_contacorrente WHERE lote_unidade=1001 AND lote_data="2008-05-12" AND lote_numero=3 UNION SELECT hash_origem FROM mov_contacorrente WHERE lote_unidade=1001 AND lote_data="2008-05-12" AND lote_numero=3 UNION SELECT cc_hash_baixa FROM dig_baixas WHERE lote_unidade=1001 AND lote_data="2008-05-12" AND lote_numero=3 UNION SELECT cc_hash_baixa FROM mov_baixas WHERE lote_unidade=1001 AND lote_data="2008-05-12" AND lote_numero=3) GROUP BY doc_hash_key

Suggested fix:
i think that some optimize options could be used for example
key in (SELECT keys from table)
should use the table

and 

key in (SELECT SQL_BUFFER_RESULT keys from table)
should use the "buffered result" instead of table

this is the same thing i did with 
from (select keys from table) as c where ....

Thank you for a problem report. Please, send the EXPLAIN results for both queries on your real data.

Does your fast query return any rows? I had noted "Impossible WHERE noticed after reading const tables" in the EXPLAIN results for it.

no rows in any select
the fast does that couldn't return nothing
the slow wait some time and return no row
may be a optimization of subselect or other optimization could allow the slow to be faster

Query rewrite in cases like this would be a nice feature to have. So, I think this is a reasonable feature request.

any idea how to implement?
maybe per subselect optimization SQL_BUFFERED_SUBQUERY could help a lot! using it the optimizer rewrite query to use a IN (value1,value2,value3,value4....)

i will close this bug, since bug http://bugs.mysql.com/bug.php?id=24770 (bug 24770) is something similar and have some worklogs