![]() ![]() How can we solve this ? The first solution is to hint MySQL to use key1 as shown below. ![]() So by trying to avoid a sort, the optimizer ends-up losing time scanning the table. What ? The query is not using the index key1, but is scanning the whole table (key: PRIMARY in above EXPLAIN) ! How can this be ? The short explanation is that the optimizer thinks - or should I say hopes - that scanning the whole table (which is already sorted by the id field) will find the limited rows quick enough, and that this will avoid a sort operation. Mysql> EXPLAIN SELECT * FROM _test_jfg_201907 The SHOW CREATE TABLE for our table is below. Now that we know what are the Query Optimizer and a Query Execution Plan, I can introduce you to the table we are querying. Luckily, MySQL provides you a way to trace the optimizer so that you can find out why.Sometimes, the MySQL Optimizer chooses a wrong plan, and a query that should execute in less than 0.1 second ends-up running for 12 minutes!This is not a new problem: bugs about this can be traced back to 2014, and a blog post on this subject was published in 2015.But even if this is old news, because this problem recently came yet again to my attention, and because this is still not fixed in MySQL 5.7 and 8.0, this is a subject worth writing about.īefore looking at the problematic query, we have to say a few words about the optimizer.The Query Optimizer is the part of query execution that chooses the query plan.A Query Execution Plan is the way MySQL chooses to execute a specific query.It includes index choices, join types, table query order, temporary table usage, sorting type … You can get the execution plan for a specific query using the EXPLAIN command. It is the job of the MySQL optimizer to quickly choose the best strategy to retrieve results of queries.Īs a Data Guardian, you will need to be able to dig into the details of why MySQL chooses a particular path. Query performance is critical to your application performance. In the optimizer, the lower cost wins! Conclusion What you are typically looking for is the cost associated with each option. It contains information for each path the optimizer could choose to retrieve results. This helps avoid trace penalties while you continue investigating the query.Īs I mentioned, the output is very long. So once you have run your query and retrieved the results, you can turn off the trace feature. You can retrieve the results of the trace from the INFORMATION_SCHEMA.OPTIMIZER_TRACE table. Then you run the query you want to trace. Enable MySQL optimizer tracingīe warned though, the output can be long. This is achieved by setting optimizer_trace="enabled=on". To trace the MySQL optimizer, you have to turn it on for your session. Luckily, you have the ability to trace the MySQL optimizer to find out why. Unfortunately, EXPLAIN won’t give you the why, only the how. When selecting for that user, MySQL could decide it costs less to do a full table scan than using that nice user_id index you have on the table. It’s early stage of your application, so you have one user that contains 90% of your activity. Imagine if you have a table that tracks all of your user activity. Because the values change the selectivity. It’s also common that the path can change based on the values of the query. ![]() Some times, it is because the optimizer chose a different path. This has told you how MySQL has resolved the query.īut have you ever looked at a query that suddenly started performing poorly, and been left wondering why? Well, if you have ever tried investigating the performance of a query, I assume you have looked at the EXPLAIN output. The optimizer determines the cost to retrieve results. It’s a bit of light reading that requires at least 2 cups of coffee! Why trace the optimizer If you are interested, the MySQL documentation shows the different primary optimizations, as well as other optimizations. In really simple terms, it estimates a cost for each potential path to get the query results based on how the query is constructed. To keep query performance fast, MySQL has to make a quick decision of which path to take. The number of ways to get the results gets even more complicated if the query contains subqueries or joins. There are different types of scans across the rows that can be used which depends on the filters in the WHERE clause. Simple queries can either be a full table scan, or utilize an index. So, query performance is critical for any serious application.Ī database like MySQL will likely have multiple ways to retrieve the results of a query. ![]() It is unlikely that you want to make your users wait more than a few milliseconds to load that data. A typical application will make many calls to the database to gather information for the end user. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |