Bug #36660 SUBQUERY slow

• Submitted: 12 May 2008 6:45
• Modified: 27 Jul 2008 18:49
• Reporter: Roberto Spadim (Basic Quality Contributor)
• Status: Verified
• Category: Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.1.20
• OS: Linux
• Assigned to: Evgeny Potemkin
• Tags: qc
• Triage: Triaged: D5 (Feature request)

[12 May 2008 6:45] Roberto Spadim

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

[17 Jul 2008 21:07] Valeriy Kravchuk

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

[19 Jul 2008 10:13] Valeriy Kravchuk

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

[20 Jul 2008 2:42] Roberto Spadim

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

[27 Jul 2008 18:49] Valeriy Kravchuk

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