Сергей Петров Сергей Петров - 3 months ago 8
MySQL Question

SQL QUERY - UNION - multiple same tables takes long time

I have a big problem with my query to the database.

I explained my problem, I have 11 identical tables (backups). The tables are exactly the same and I need to pull data from them, but it takes a very long time.

I'm using union query like this:

select id, name, ... from table_1 WHERE (...)
UNION
select id, name, ... from table_3 WHERE (...)
UNION
select id, name, ... from table_2 WHERE (...)
... ORDER BY created DESC LIMIT 0,50


But only query takes about 16 seconds! The database does not have that much data that it took so long ...

table_1 = 1.3k
table_2 = 17k
table_3 = 10k
table_4 = 10k
... = 10k
table_11= 140K


Can you help me to optimize this query? Thanks a lot!

Answer

First, don't use UNION -- unless you want to incur the overhead to remove duplicates. Instead, use UNION ALL:

select id, name, ... from table_1 WHERE (...)
UNION ALL
select id, name, ... from table_3 WHERE (...)
UNION ALL
select id, name, ... from table_2 WHERE (...)
...
ORDER BY created DESC
LIMIT 0, 50;

You may also be able to improve performance by using indexes appropriate for the WHERE clause. However, your question does not have enough information for that.

Actually, another optimization is to take the first 50 rows from each subquery before the union:

(select id, name, ... from table_1 WHERE (...) ORDER BY created DESC LIMIT 50)
UNION ALL
(select id, name, ... from table_3 WHERE (...) ORDER BY created DESC LIMIT 50)
UNION ALL
(select id, name, ... from table_2 WHERE (...) ORDER BY created DESC LIMIT 50)
...
ORDER BY created DESC
LIMIT 0, 50;

This reduces the amount of data for the final ORDER BY, which should also speed things up.