Slow Query Basics: Why Are Queries Slow?

Slow Query Basics: Why Are Queries Slow?

·

7 min read

Introduction

​ If you've ever worked with databases, you have probably encountered some queries that seem to take longer to execute than they should. Queries can become slow for various reasons ranging from improper index usage to bugs in the storage engine itself. However, in most cases, queries become slow because developers or MySQL database administrators neglect to monitor them and keep an eye on their performance. In this blog, we will figure out how to avoid that. ​

What is a Query?

​ To understand what makes queries slow and improve their performance, we have to start from the very bottom. First, we have to understand what a query fundamentally is–a simple question, huh? However, many developers and even very experienced database administrators could fail to answer. ​

MySQL Query Execution Sequence

​ Queries are tasks that are composed of smaller tasks. If we want to improve their performance, we make those smaller tasks shorter or eliminate them. Here’s how MySQL executes its queries: ​

  1. First and foremost, MySQL sends the SQL statement to the server for execution.
  2. MySQL checks the query cache. If the results exist there, they are returned. If not, MySQL proceeds to the next step.
  3. The server parses a given SQL query, and a query optimization plan is made.
  4. The plan is executed, calls to storage engines are made.
  5. Storage engines return rows – we see results! ​

However, to observe how MySQL executes its queries on a deeper level, we should probably profile them instead of simply looking at the shiny outside of them. That’s where the real fun begins. ​

Profiling Queries

​ By now, we have touched the outside of what makes queries run. Still, to optimize our slow queries, we must first understand what they do and what stages of execution they go through before trying to optimize anything – in other words, profile them. ​

Thankfully, if we want to make use of the power offered by profiling in MySQL, we won’t have a hassle – it’s really, really easy to do. ​​

Using SHOW PROFILES in MySQL

​ Enable profiling by running SET profiling = 1;, run your query, then issue a SHOW PROFILES; on MySQL. You should now be able to see through what stages your MySQL query went through and how long each of those stages took to execute (the table below represents states and durations returned when a SELECT query was profiled – the entire table is actually quite a bit larger, but since we are only interested in the states and durations, we are going to be observing that):

StateDuration
Starting0.000017
Waiting for query cache lock0.000004
Waiting on query cache mutex0.000006
Checking query cache for query0.000027
Checking permissions0.000005
Opening tables0.000276
System lock0.000010
Waiting for query cache lock0.000022
Waiting on query cache mutex0.000021
Init0.000007
Optimizing0.000005
Statistics0.000004
Preparing0.000055
Executing0.000042
Sending data0.000004
End0.000002
Query end0.000067
Closing tables0.000005
Freeing items0.000056
Logging slow query0.000001
Cleaning up0.000002

See what we meant by saying that “queries are tasks that are composed of smaller tasks”? These smaller tasks are listed above. Now that we know what tasks our query goes through before the results reach our screens, it should be easier to optimize. ​

Optimizing Your Queries

​ To optimize your query performance, make the execution time of those tasks shorter or make MySQL not execute them at all. “How do I do that?” – you ask? There are a couple of ways to navigate around this complex jungle: ​

ActionExplanation
Make sure to use appropriate permissionsChecking permissions is one of the first things that happen when a query is executed. By ensuring that the user you run queries with has proper permissions, you can shorten the task.
Keep the query cache big enoughMake use of the query_cache_size variable if you are running MySQL <= 5.7.20. Though note that this variable was removed in MySQL 8.
Properly use indexes and partitionsWhile both of them would slow down the performance of INSERTs, UPDATEs, and DELETEs, they would speed up read (SELECT) queries, sometimes by an order of magnitude. With indexes, MySQL will be able to execute SELECT queries faster because it will scan through less data – as a result, your queries will be faster. If you want to, you can also log all of the queries not using indexes by using the log_queries_not_using_indexes parameter, but for that, you would need to enable the slow_query_log parameter (set it to a value of ON).
Don’t ask MySQL for data you don’t needInstead of using SELECT *, select only the columns that are necessary for your query to execute successfully. In other words, don’t make MySQL examine data that is not necessary.
If you want to, enable the slow query log.As you can see, if a query is slow, it’s logged in the slow query log (it’s one of the last tasks that gets executed) – keep your slow_query_log variable ON and perhaps adjust how MySQL understands what a “slow query” is: by default, MySQL logs any query that runs for longer than 10 seconds, but this value can be adjusted by adjusting the long_query_time parameter.

What to Do After Profiling

​ Once you have profiled your queries, you should be set on a good path. You should be set on a good path because profiling helps you look at the past of your queries to make them performant in the future. However, there are a couple of additional things you would need to keep a watchful eye on:

What to Keep an Eye On?Why?
Schema and Data TypesProperly optimizing your schema and data types is crucial for high performance; do not ever neglect them – you will make your own life as a DBA harder. If you neglect to keep an eye on those things, chances are you will waste hard drive space and potentially degrade the performance of your database instances in the future.
Character Sets and CollationsMySQL supports many different character sets and collations. Each of them must be used carefully both to save space and to avoid negatively impacting performance. In this case, avoid using character sets and collations of unknown descent to you; consult the MySQL manual or blogs like this one, learn and then apply your knowledge.
IndexesMySQL supports many kinds of indexes that could be twisted in such a way that helps accomplish a variety of goals: of course, they have their own upsides and downsides. However, in most of the use cases, they can dramatically improve performance–we have dug into indexing in an older blog post, so for further explanation, have a read.
PartitionsPartitions, of course, are good friends of indexes, and they can also help improve query performance–however, only when used properly. For example, they can allow MySQL to back up or recover data relevant to one partition and not all data at once. We have dug into partitions earlier on, so have a read if you want to figure out how it works on a deeper level.
Server SettingsServer settings are a cornerstone of your MySQL instance–always keep an eye out for them. We will have a blog post focused on this specific topic, but for now, learning how to optimize InnoDB for big data sets should do the trick.
HardwareHardware sits underneath everything you do in MySQL–by having proper hardware, you can properly optimize your server settings for high performance. Therefore, always use as much hardware as necessary and allocate enough resources to MySQL to make it run at the very best of its ability.
Blogs and DocumentationOf course, blogs like the Arctype blog are also a perfect place to expand your knowledge. They can tell you how you should work with your database instances to improve your database performance on the go. Always keep an eye on documentation, too– it’s vital for your database both now and in the future.

Take care of all of the aforementioned things, and once you have, make sure to use a proper SQL client!

Lukas is an ethical hacker, a MySQL database administrator, and a frequent conference speaker. Since 2014 Lukas has found and responsibly disclosed security flaws in some of the most visited websites in Lithuania and abroad including advertising, gift-buying, gaming, hosting websites as well as some websites of government institutions. Lukas runs one of the biggest & fastest data breach search engines in the world - BreachDirectory.com and frequently blogs in multiple places educating people about information security and other topics. He also runs his own blog over at lukasvileikis.com