Menu
I have a SQL query that has 4 UNIONS and 4 LEFT JOINS. It is layed out as such:
SELECT ... FROM table1
LEFT JOIN other_table1
UNION SELECT ... FROM table2
LEFT JOIN other_table2
UNION SELECT ... other_table3
LEFT JOIN other_table3
UNION SELECT ... FROM table4
LEFT JOIN other_table4
Would it be better to run 4 separate queries and then merge the results with php after the fact? Or should I keep them together? Which would provide that fastest execution?
859
A UNION statement effectively does a SELECT DISTINCT on the results set. If you know that all the records returned are unique from your union, use UNION ALL instead, it gives faster results.
Preserving performance through UNION The UNION operation allows us to merge the results of two queries. Since we know that query #1 and query #3 are each significantly faster than query #2, we would expect that the results of the UNION operation will be fast as well.
Save this question. Show activity on this post. While each of both select statements takes less than 1 second when executed separately.
Both UNION and UNION ALL operators combine rows from result sets into a single result set. The UNION operator removes eliminate duplicate rows, whereas the UNION ALL operator does not. Because the UNION ALL operator does not remove duplicate rows, it runs faster than the UNION operator.
The most definitive answer is to test each method, however the UNION is most likely to be faster as only one query is run by MySQL as opposed to 4 for each part of the union.
You also remove the overhead of reading the data into memory in PHP and concatenating it. Instead, you can just do a while() or foreach() or whatever on one result.
195
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
