|

How to Optimize MySQL for High Speed

There is no need to mention that speed is one of the most sought-after attributes in any database system. Optimizing MySQL for high speed is essential to ensure responsive applications and efficient data processing.

There are basically two best ways to speed up UNION in MySQL – using UNION ALL and using push-down conditions.

In this article, we’ll explore techniques to achieve optimal performance and speed in MySQL.

How to Optimize MySQL

Ways to Optimize MySQL for High Speed

Here’s a rundown of how to do those optimizations.

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
blac

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.
Queries with UNION can be accelerated in two ways. Switch to UNION ALL or try to push ORDER BY, LIMIT, and WHERE conditions inside each subquery. You’ll be glad you did!

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.

Frequently Asked Questions (FAQs)

Which Tool Optimize MySQL Query?

There are several MySQL optimizations tools to analyze SQL queries efficiently. One such tool is SolarWinds® Database Performance Analyzer (DPA), which provides collated data on SQL statements with second-by-second accuracy.

Which MySQL Engine Is Faster?

MyISAM is one of the fastest MySQL engines for database writers. However, you can also put InnoDB in the list. Although InnoDB is slower than MyISAM (INSERT and UPDATE), but its transaction protocol is still better than any other engines.

Conclusion

That’s all about how you can optimize MySQL Union for high speed. Remember multi-million-row seats 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.

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *