MySQL UNION ALL performance tuning

Question

SELECT `col1` FROM `tbl1`;   -- takes 0.0022s

SELECT `col1` FROM `tbl2`;   -- takes 0.0017s

SELECT `col1` FROM `tbl1`
UNION ALL 
(SELECT `col1` FROM `tbl2`); -- takes 0.1100s

Why UNION ALL working slow?

Any other alternative of using UNION ALL?

@scaisEdge It does not matter, after removing it returning the same output costing same time — MD. Kamruz Zaman Rana, Sep 18 '17 at 07:32
@MKhalidJunaid Can you elaborate? I used explain sql but i wanted to know the reason why union all slows two sql down, — MD. Kamruz Zaman Rana, Sep 18 '17 at 07:35
@MD.KamruzZamanRana To know the reason first you have to look into [Obtaining Execution Plan Information](https://dev.mysql.com/doc/refman/5.7/en/explain.html) — M Khalid Junaid, Sep 18 '17 at 07:39
@scaisEdge - I respond to your suggestion on the () in my Answer. — Rick James, Sep 18 '17 at 23:12

score 2 · Answer 1 · edited Jun 10 '21 at 00:09

Is the Query Cache turned on? It could be that the two individual SELECTs are artificially fast due to getting the resultset from the QC.

Let's dissect the UNION to understand its sluggishness:

Create a temp table for the resultset.
Perform first SELECT, writing results to that temp.
Perform second SELECT, writing results to that temp.
Read the temp table.
(De-duplicate. This did not happen since you said ALL on UNION.)
Deliver the results to the client.
Drop the temp table.

Two things are probably aggravating the situation:

Windows is slower than *NIX at building tables.
It was not until a very recent versions of MySQL that some UNIONs can avoid the temp table. That is, in the future, the results from each SELECT can be delivered directly to the client. You apparently do not have such version.

As for the parentheses, leave them on. Put them on the other SELECT, too. It does not impact performance, but it may impact the result. Imagine, for example, what the GROUP BY applies to in these three cases:

-- Case 1
SELECT ..     UNION SELECT ..   GROUP BY ..

-- Case 2
SELECT ..     UNION ( SELECT ..   GROUP BY .. )

-- Case 3
( SELECT .. ) UNION ( SELECT .. ) GROUP BY ..

The first one is the same as the second, not the third.

MySQL UNION ALL performance tuning

1 Answers1