Menu
I'm interested to know if a UNION is better than running multiple queries.
If so, are there times that multiple queries would be faster or preferred for other reasons?
872
The main reason for the union sql running slower is that a union causes mysqld to create an internal temporary table. It creates just a table for a UNION ALL and a table with an index (to remove duplicates) for a UNION DISTINCT. Hope this helps.
UNION ALL is faster than UNION because plain UNION is expecting that within two joined datasets are duplicates which need to be removed. If you can ensure (by inner WHERE clauses) that there will be no duplicates, it's far better to use UNION ALL and let database engine optimize the inner selects.
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.
Union will be faster, as it simply passes the first SELECT statement, and then parses the second SELECT statement and adds the results to the end of the output table.
UNION ALL and don't sort the output, then the
performance of UNION should be more-or-less the same as the
performance of multiple separate queries assuming the queries are
identical to the ones you're UNION-ing together.UNION ALL and sort the data, obviously you're imposing some overhead
(although probably less than if you sorted in it your application).ALL keyword, MySQL will do the extra work of
DISTINCT-ing your results. Again, this imposes extra overhead
although probably less than doing it yourself.
58
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
