UNION ALL MYSQL is really slow

Question

I have a query in mysql with 2 select from's. When I run these queries individually they run quick within 1 second. But when I combine them with union all, the website freeze's and it takes atleast 20 seconds for the same query to execute in union all.

Any idea why this happens? Can't figure it out.

See the query below:

SELECT p.user_id, p.description, p.post_id, p.created_at, ps.username AS size_name, ps.username AS user_name, ps.avatar AS avatar, pz.title AS title, pz.slug AS slug 
FROM comments p 
JOIN users ps ON ps.id = p.user_id 
JOIN posts pz ON pz.id = p.post_id 

UNION ALL

SELECT p2.user_id, p2.description, p2.post_id, p2.created_at, ps2.username AS size_name, ps2.username AS user_name, ps2.avatar AS avatar, pz2.title AS title, pz2.slug AS slug 
FROM reply p2 
JOIN users ps2 ON ps2.id = p2.user_id 
JOIN posts pz2 ON pz2.id = p2.post_id 

order by created_at DESC 
LIMIT 10

Do ORDER BY and LIMIT 10 in each SELECT first. Then do it on the UNION ALL result too. — jarlh, Dec 21 '20 at 20:54
Your problem is the ORDER BY clause. The UNION needs to get all records, and then sort them. — Sourcerer, Dec 21 '20 at 20:58

score 2 · Accepted Answer · answered Dec 21 '20 at 20:57

2

Before merging with UNION, reduce the size of the tables returned by each subquery. Since you only want the top 10, you only need the top 10 of each subquery.

SELECT *
FROM (
    (SELECT p.user_id, p.description, p.post_id, p.created_at, ps.username AS size_name, ps.username AS user_name, ps.avatar AS avatar, pz.title AS title, pz.slug AS slug 
    FROM comments p 
    JOIN users ps ON ps.id = p.user_id 
    JOIN posts pz ON pz.id = p.post_id 
    ORDER BY created_at DESC
    LIMIT 10) 
    
    UNION ALL
    
    (
    SELECT p2.user_id, p2.description, p2.post_id, p2.created_at, ps2.username AS size_name, ps2.username AS user_name, ps2.avatar AS avatar, pz2.title AS title, pz2.slug AS slug 
    FROM reply p2 
    JOIN users ps2 ON ps2.id = p2.user_id 
    JOIN posts pz2 ON pz2.id = p2.post_id 
    ORDER BY created_at DESC
    LIMIT 10)
) AS x
order by created_at DESC 
LIMIT 10

answered Dec 21 '20 at 20:57

Barmar

741,623
53
500
612

you can just use parentheses, not a subquery, to have order by/limit for the individual queries; see my answer – ysth Dec 21 '20 at 21:23
But you need an outer select to filter it to the top 10 of the combined queries. – Barmar Dec 21 '20 at 21:31
no, you don't. you can order & limit the individual queries and order and limit the union as a whole too – ysth Dec 21 '20 at 21:40
oh, didn't know you could do it like you show. – Barmar Dec 21 '20 at 21:43

ysth · Answer 2 · 2020-12-21T21:42:50.847

1

UNION defaults to UNION DISTINCT. If you want UNION ALL, explictly say that (and you should, wherever possible, since UNION DISTINCT has to do a lot of work to make them distinct).

The other thing is that the order by and limit apply to the query as a whole. You may want to make each unioned query have them too. See https://dev.mysql.com/doc/refman/8.0/en/union.html:

ORDER BY and LIMIT in Unions

To apply an ORDER BY or LIMIT clause to an individual SELECT, parenthesize the SELECT and place the clause inside the parentheses:

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

If your fast individual versions did have those, that should help. So something like this:

(SELECT p.user_id, p.description, p.post_id, p.created_at, ps.username AS size_name, ps.username AS user_name, ps.avatar AS avatar, pz.title AS title, pz.slug AS slug 
FROM comments p 
JOIN users ps ON ps.id = p.user_id 
JOIN posts pz ON pz.id = p.post_id 
order by created_at DESC 
LIMIT 10)

UNION ALL

(SELECT p2.user_id, p2.description, p2.post_id, p2.created_at, ps2.username AS size_name, ps2.username AS user_name, ps2.avatar AS avatar, pz2.title AS title, pz2.slug AS slug 
FROM reply p2 
JOIN users ps2 ON ps2.id = p2.user_id 
JOIN posts pz2 ON pz2.id = p2.post_id 
order by created_at DESC 
LIMIT 10)

order by created_at DESC 
LIMIT 10

edited Dec 21 '20 at 21:42

answered Dec 21 '20 at 20:49

ysth

96,171
6
121
214

I forgot to add the UNION ALL here on stackoverflow. I changed it. Thank you. – Naklov Dec 21 '20 at 20:53
That query will return just 2 rows; 10 and 11. – jarlh Dec 21 '20 at 21:29
This will return up to 20 rows. – Barmar Dec 21 '20 at 21:33
@Barmar, the first SELECT will return up to 10 rows, all value 10. The second SELECT will return up to 10 rows, all value 11. **UNION** will remove duplicates and return just one 10 and one 11 (if both values exist.) – jarlh Dec 21 '20 at 21:35
That's not representative of the original query, since they want the top 10 rows from two combined queries. – Barmar Dec 21 '20 at 21:38
since the query from the documentation seemed to confuse some of you, added one customized to this question – ysth Dec 21 '20 at 21:43

UNION ALL MYSQL is really slow

2 Answers2