CODEWITHSUNDEEP
mysqlselectunion
knzo
knzo

Reputation: 65

MYSQL: Query 2 tables with union is very slow, how to improve?

I want to query 2 tables with (almost) identical rows at the same time. As a result, I want to get the 5 recent entries (ordered by date, in total), no matter from which table they are from

So far, I tried this:

SELECT date, name, text FROM `table_A` 
UNION 
SELECT date, name, text FROM `table_B` ORDER BY date desc LIMIT 5

Unfortunately, this query takes about 20 seconds (both tables have ~300.000 rows).

When I just do:

SELECT date, name, text FROM `table_A` ORDER BY date desc LIMIT 5

or

SELECT date, name, text FROM `table_B` ORDER BY date desc LIMIT 5

the query takes only a few milliseconds.

So my question is: How can I improve my query to be faster or what select query should I use to get the 5 latest rows from both tables?

Upvotes: 3

Views: 987

Answers (1)

Barmar
Barmar

Reputation: 782785

Select the most recent 5 rows in each table before combining them.

SELECT *
FROM (
    (SELECT date, name, text FROM table_A ORDER BY date DESC LIMIT 5) 
    UNION
    (SELECT date, name, text FROM table_B ORDER BY date DESC LIMIT 5)
) x
ORDER BY date DESC
LIMIT 5

The problem with your query is that it's first merging the entire tables and removing duplicates before doing the ordering and limiting. The merged table doesn't have an index, so that part is slow.

Upvotes: 4

Related Questions