UNION ALL and LIMIT in MySQL

It works for me I'm using MySQL.

but make sure the limit number is always the same for all

in that example it gets you 3 results from each table

 (SELECT a FROM t1 WHERE a=10 AND B=1 LIMIT 9) 
 UNION ALL 
 (SELECT a FROM t2 WHERE a=11 AND B=2 LIMIT 9) 
 UNION ALL 
 (SELECT a FROM t3 WHERE a=12 AND B=3 LIMIT 9)

If you have a very large table, then you can execute following query to see how smart MySQL optimizer is...

(
  select * from very_large_table
  union all
  select * from very_large_table
) limit 1

Sadly, this query takes very long time, compared to following query.

select * from very_large_table limit 1

It seems that whenever result of UNION is needed, MySQL first calculates the entire temporary table of the UNION result.

version

  • MySQL 8.0.11

Currently, MySQL will perform all selects on a union even if there are enough rows in the first few queries, as @Yuki Inoue mentioned in their answer. Using @user1477929's answer, you could re-write your query as:

(SELECT a FROM t1 WHERE a=10 AND B=1 LIMIT 1000) 
UNION ALL 
(SELECT a FROM t2 WHERE a=11 AND B=2 LIMIT 1000) 
UNION ALL 
(SELECT a FROM t3 WHERE a=12 AND B=3 LIMIT 1000) 
ORDER BY a LIMIT 1000;

which will give you at most 1000 rows, and never scan more than 3000.


As specified in UNION Syntax description (http://dev.mysql.com/doc/refman/5.1/en/union.html):

The default behavior for UNION is that duplicate rows are removed from the result. The optional DISTINCT keyword has no effect other than the default because it also specifies duplicate-row removal. With the optional ALL keyword, duplicate-row removal does not occur and the result includes all matching rows from all the SELECT statements.

I suppose, that's the answer to your question.

Tags:

Mysql

Recent Posts