How to Optimize MySQL UNION for High Speed
How to Optimize MySQL UNION for High Speed
Join the DZone community and get the full member experience.
Join For FreeSensu is an open source monitoring event pipeline. Try it today.
There are two ways to speedup UNIONs in a MySQL database. First use UNION ALL if at all possible, and second try to push down your conditions.
1. UNION ALL is much faster than UNION
How does a UNION work? Imagine you have two tables for shirts. The short_sleeve table looks like this:
blue green gray black
And long_sleeve another that looks like this:
red green yellow blue
Related: Why Generalists are Better at Scaling the Web
If you UNION those two tables, first MySQL will sort the combined set into a temp table like this:
black blue blue gray green green red yellow
Once it’s done this sort, it can easily remove the duplicate blue & duplicate green for this resulting set:
black blue gray green red yellow
See also: Mythical MySQL DBA – the talent drought.
Why does it do this? UNION is defined that way in SQL. Duplicates must be removed and this is an efficient way for the MySQL engine to remove them. Combine results, sort, remove duplicates and return the set.
What if we did UNION ALL? The result would look like this:
blue green gray black red green yellow blue
Read this: MySQL DBA Interview & Hiring Guide.
It doesn’t have to sort, and doesn’t have to remove duplicates. If you imagine combining two 10 million row tables, and don’t have to sort, this speedup can be HUGE.
2. Use Push-down Conditions to speedup UNION in MySQL
Imagine with our example above the shirts have a design date, the year they were released. Yes we’re keeping this example very simple to illustrate the concept.
Here is the short_sleeve table:
blue 2013 green 2013 green 2012 gray 2011 black 2009 black 2011
And long_sleeve table looks like this:
red 2012 red 2013 green 2011 yellow 2010 blue 2011
For 2013 designs could combine them like this:
(SELECT type, release FROM short_sleeve) UNION (SELECT type, release FROM long_sleeve); WHERE release >=2013;
See also: 5 More Things Deadly to Scalability and the original 5 Things Toxic to Scalability..
Here the WHERE clause works on this 11 record temp table:
black 2009 black 2011 blue 2011 blue 2013 gray 2011 green 2013 green 2012 green 2011 red 2012 red 2013 yellow 2010
But it would be much faster to move the WHERE inside each subquery like this:
(SELECT type, release FROM short_sleeve WHERE release >=2013) UNION (SELECT type, release FROM long_sleeve WHERE release >=2013);
That would be operating on a combined 3 record table. Faster to sort & remove duplicates. Smaller result sets cache better too, providing a pay forward dividend. That’s what performance optimization is all about!
Read this: RDS or MySQL – 10 Use Cases.
Remember multi-million row sets in each part of this query will quickly illustrate the optimization. We’re using very small results to make visualizing easier.
You can also use this optimization for ORDER BY and for LIMIT conditions. By reducing the number of records returned by EACH PART of the UNION, you reduce the work that happens at the stage where they are all combined.
If you’re seeing some UNION queries in your slow query log, I suggest you try this optimization out and see if you can tweak it.
Sensu: workflow automation for monitoring. Learn more—download the whitepaper.
Like This Article? Read More From DZone
Published at DZone with permission of Sean Hull . See the original article here.
Opinions expressed by DZone contributors are their own.
{{ parent.title || parent.header.title}}
{{ parent.tldr }}
{{ parent.linkDescription }}
{{ parent.urlSource.name }}