Optimize MySQL Union Query
Posted On: April 29, 2014
Problem: single queries run fast, but when you put them inside a union, it takes hundreds of times longer. A potential solution is below.
Here is query we used:
( SELECT coumn1, otherColumn AS column2, column3 FROM tableName)
UNION
( SELECT coumn1, otherColumn2 AS column2, column3 FROM tableName2)
WHERE column3 = 5 ORDER BY coumn1 LIMIT 0, 10
Run time*: 1 sec
1. Try to use UNION ALL instead of UNION.
What is the difference between UNION and UNION ALL? In simple words, UNION removes all duplicates records, and UNION ALL does not. Duplicate records are records where all columns are the same.
( SELECT coumn1, otherColumn AS column2, column3 FROM tableName)
UNION ALL
( SELECT coumn1, otherColumn2 AS column2, column3 FROM tableName2)
WHERE column3 = 5 ORDER BY coumn1 LIMIT 0, 10
2. Move WHERE close into individual queries.
( SELECT coumn1, otherColumn AS column2, column3 FROM tableName WHERE column3 = 5)
UNION ALL
( SELECT coumn1, otherColumn2 AS column2, column3 FROM tableName2 WHERE column3 = 5)
ORDER BY coumn1 LIMIT 0, 10
3. Move LIMIT into the individual queries
( SELECT coumn1, otherColumn AS column2, column3 FROM tableName WHERE column3 = 5 ORDER BY coumn1 LIMIT 0, 10)
UNION ALL
( SELECT coumn1, otherColumn2 AS column2, column3 FROM tableName2 WHERE column3 = 5 ORDER BY coumn1 LIMIT 0, 10)
ORDER BY coumn1 LIMIT 0, 10
This was the main trick that helped us. A couple of things to keep in mind:
- Every inner query needs to have its limit set to the maximum you need to display, in case the other one has less items then you need.
- You need to include ORDER BY in each query as well
At the end our runtime got to 0.04 seconds.
Potential number 4: move ORDER BY to individual queries, but this is not always possible.
*Run time is average run time out of 1000 runs. tableName has 100,000 records and about same in tableName2
Please keep in mind that those tricks may not help in all cases, but in some it will be very helpful.
Associated tags:
MySQL,
UNION,
UNION ALL
Comments:
Vlads wrote on
May 1, 2014 at 12:19
Thanks for the info.
What works faster:
FROM table1 JOIN table2 USING(id) WHERE
OR
FROM table1, table2 WHERE table1.id= table2.id?
Michael Pankratovs wrote on
May 1, 2014 at 12:32
Hi Vlad,
Thanks for the question. Here is an answer to it:
What is faster, JOIN or SELECT from multiple tables?
