MySQL Bugs: #60691: LIMIT 1 WITH UNION

Bug #60691	LIMIT 1 WITH UNION
Submitted:	29 Mar 2011 15:49	Modified:	29 Mar 2011 18:18
Reporter:	Roberto Spadim (Basic Quality Contributor)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S5 (Performance)
Version:	5.5	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	limit, qc, UNION

Description:
Hi guys, i´m trying to execute a UNION, and just get 1 row what´s the best way to solve this problem?

i will send my SQL and explain results it´s a ~700000 rows in this table
the diference:
table A, use = "_SOME_VALUE_" (only 1 row to examine, using primary key)
table B, use LIKE 'j;1;%;_PART_OF_SOME_VALUE_' (some rows)
table C, use LIKE 'j;1;%;%_PART_OF_SOME_VALUE_' (same some rows)
table D, use LIKE 'j;1;%;%_PART_OF_SOME_VALUE_' (same some rows)
table E, use LIKE '%;%;NF;%_PART_OF_SOME_VALUE_' (many rows!)
table F, use LIKE '%;%;%;%_PART_OF_SOME_VALUE_' (many rows!)

if table A have 1 record, table b,c,d,e,f don´t need to execute (1 record was founded)

How to repeat:
explain
SELECT cc_hash_key,devedor_tipo,devedor_id,credor_tipo,credor_id,credor_ca,credor_conta,credor_carteira,credor_convenio,devedor_ca,devedor_conta,devedor_carteira,0 AS devedor_convenio,credor_co,devedor_co 
FROM mov_contacorrente AS A WHERE cc_hash_key="j;1;NF;66811/1nf2-1 - M 447374" AND lote_tipo='r' AND lote_unidade=1001 
UNION 
SELECT cc_hash_key,devedor_tipo,devedor_id,credor_tipo,credor_id,credor_ca,credor_conta,credor_carteira,credor_convenio,devedor_ca,devedor_conta,devedor_carteira,0 AS devedor_convenio,credor_co,devedor_co 
FROM mov_contacorrente AS B WHERE cc_hash_key LIKE "j;1;NF;66811/1nf2-1 - M 447374%" AND lote_tipo='r' AND lote_unidade=1001 
UNION 
SELECT cc_hash_key,devedor_tipo,devedor_id,credor_tipo,credor_id,credor_ca,credor_conta,credor_carteira,credor_convenio,devedor_ca,devedor_conta,devedor_carteira,0 AS devedor_convenio,credor_co,devedor_co 
FROM mov_contacorrente AS C WHERE cc_hash_key LIKE "j;1;%;66811/1nf2-1 - M 447374%" AND lote_tipo='r' AND lote_unidade=1001 
UNION 
SELECT cc_hash_key,devedor_tipo,devedor_id,credor_tipo,credor_id,credor_ca,credor_conta,credor_carteira,credor_convenio,devedor_ca,devedor_conta,devedor_carteira,0 AS devedor_convenio,credor_co,devedor_co 
FROM mov_contacorrente AS D WHERE cc_hash_key LIKE "j;1;%;%66811/1nf2-1 - M 447374%" AND lote_tipo='r' AND lote_unidade=1001 
UNION 
SELECT cc_hash_key,devedor_tipo,devedor_id,credor_tipo,credor_id,credor_ca,credor_conta,credor_carteira,credor_convenio,devedor_ca,devedor_conta,devedor_carteira,0 AS devedor_convenio,credor_co,devedor_co 
FROM mov_contacorrente AS E WHERE cc_hash_key LIKE "%;%;NF;%66811/1nf2-1 - M 447374%" AND lote_tipo='r' AND lote_unidade=1001 
UNION 
SELECT cc_hash_key,devedor_tipo,devedor_id,credor_tipo,credor_id,credor_ca,credor_conta,credor_carteira,credor_convenio,devedor_ca,devedor_conta,devedor_carteira,0 AS devedor_convenio,credor_co,devedor_co 
FROM mov_contacorrente AS F WHERE cc_hash_key LIKE "%;%;%;66811/1nf2-1 - M 447374%" AND lote_tipo='r' AND lote_unidade=1001 
UNION 
SELECT cc_hash_key,devedor_tipo,devedor_id,credor_tipo,credor_id,credor_ca,credor_conta,credor_carteira,credor_convenio,devedor_ca,devedor_conta,devedor_carteira,0 AS devedor_convenio,credor_co,devedor_co 
FROM mov_contacorrente AS G WHERE cc_hash_key LIKE "%;%;%;%66811/1nf2-1 - M 447374%" AND lote_tipo='r' AND lote_unidade=1001
LIMIT 1

Suggested fix:
i don´t know if i´m using a wrong sql, or if i should rewrite using many sqls

result of explain:
"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
"1","PRIMARY","A","const","PRIMARY,lote","PRIMARY","77","const","1",""
"2","UNION","B","range","PRIMARY,lote","PRIMARY","77",NULL,"4","Using where"
"3","UNION","C","ref","PRIMARY,lote","lote","4","const","705786","Using where"
"4","UNION","D","ref","PRIMARY,lote","lote","4","const","705786","Using where"
"5","UNION","E","ref","lote","lote","4","const","705786","Using where"
"6","UNION","F","ref","lote","lote","4","const","705786","Using where"
"7","UNION","G","ref","lote","lote","4","const","705786","Using where"
NULL,"UNION RESULT","<union1,2,3,4,5,6,7>","ALL",NULL,NULL,NULL,NULL,NULL,""

Probably you need UNION ALL then, not UNION... 

See bug #20302 and bug #17842 for similar feature requests and related worklog number.

If UNION ALL will work for you, there is a bug report already in progress that (after being fixed) may help in your case also, Bug #50674.

See Bug #58924 also :)

nice i´m testing union all, just some minutes...

didn´t work 18 seconds, if only run query 1 it get 0,1 seconds (others queries are very slow i know, it´s near a table scan)

explain result:

"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
"1","PRIMARY","A","const","PRIMARY,lote","PRIMARY","77","const","1",""
"2","UNION","B","range","PRIMARY,lote","PRIMARY","77",NULL,"4","Using where"
"3","UNION","C","ref","PRIMARY,lote","lote","4","const","705793","Using where"
"4","UNION","D","ref","PRIMARY,lote","lote","4","const","705793","Using where"
"5","UNION","E","ref","lote","lote","4","const","705793","Using where"
"6","UNION","F","ref","lote","lote","4","const","705793","Using where"
"7","UNION","G","ref","lote","lote","4","const","705793","Using where"
NULL,"UNION RESULT","<union1,2,3,4,5,6,7>","ALL",NULL,NULL,NULL,NULL,NULL,""

BUG 58924 seens a bit confusing, i don´t know if he/she want limit the first select, the second, or the result of union
BUG 50674 seens to a algorithm selection, temp table have some pros and cons, nothing to do here, maybe a SQL option SQL_SMALL_RESULT or SQL_BIG_RESULT could help optimizer to choose between temp table or not, i prefer temp table today, in my opnion BIG UNIONs aren´t fast, a temporary table and after a select is better
MY BUG IS DUPLICATED OF BUG 20302, I WILL CLOSE IT (the problem continue in 5.5)
BUG 17842 could be better optimized with PARTITION engine with different engines using partition prune (i don´t know if UNION is a good choise)

the idea here is the union (union all could work too)
thanks ! :) mark as duplicated of BUG 20302 please