UNION ALL and LIMIT in MySQL

Let's say I want to perform this query:

(SELECT a FROM t1 WHERE a=10 AND B=1) 
UNION ALL 
(SELECT a FROM t2 WHERE a=11 AND B=2) 
UNION A开发者_开发技巧LL 
(SELECT a FROM t3 WHERE a=12 AND B=3) 
ORDER BY a LIMIT 1000;

Is MySQL smart enough to skip "t3" if 550 results are available in "t1" and 450 in "t2"?

I'm looking at MySQL docs (http://dev.mysql.com/doc/refman/5.1/en/union.html) but can't seem to find the answer.

---

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.

---

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

---

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.

---

Yes, MySql is smart enough to skip t3 if the results returned from t1 and t2 are equal to the LIMIT. In fact, it's the default behavior.