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
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