Guide To Mastering The Mysql Explain Plan

1. Guide To Mastering The MySQL Query Execution Plan In this PPT, we will go in-depth into the world of MySQL query execution plan. We will break it down into its fundamental concepts and learn how it works and how to make use of it in our SQL optimization processes.

2. The Purpose of MySQL query execution plan MySQL query optimizer is an in-built function of the database which automatically runs when you execute a query. Its job is to design an optimal MySQL query execution plan for every single query that is executed. The MySQL explain plan allows you to view the plan by using the EXPLAIN  keyword as a prefix to your request.

3. What Is MySQL Explain plan ? • The EXPLAIN  keyword is an extremely powerful tool to understand and optimize MySQL queries. They offer explanations and insights as to why your queries are slow or performing poorly. However, we have seen DBAs and developers rarely use it. Since you are it’s it’s a sign that you want to make your queries faster. So, let’s get into how we can interpret the results the EXPLAIN statement gives us. EXPLAIN ANALYZE is a profiling tool for your queries that will show you where MySQL spends time on your query and why. It will plan the query, instrument it and execute it while counting rows and measuring time spent at various points in the execution plan. When execution finishes, EXPLAIN ANALYZE will print the plan and the measurements instead of the query result. • The Right Way to Interpret the EXPLAIN results • In our daily life, we generally tend to inquire about the cost of goods before we actually purchase them. Similarly, in the MySQL explain plan realm, the EXPLAIN tool helps to fetch the running cost of a query before it’s actually executed. • The  EXPLAIN  tool in MySQL describes how the DML will be executed and that includes the table structure as well. It’s key to note here that since MySQL 5.7, the DML (Select, Update, Delete, Insert, and Replace) commands are allowed in EXPLAIN—Thus,  we will not just mention SELECT in our explanations.

4. Achieving High Performance through Data Indexing: Let’s begin by analyzing the output of a simple query that uses the EXPLAIN keyword and then work our way towards more complicated ones.  Before we proceed, it’s key to ensure that you have the SELECT privilege to use the EXPLAIN tool and the SHOW VIEW privilege for working with views. Here’s an example: Since we have used  EXPLAIN in the query above,  we are able to see the tables where indexes are missing. This allows you to make the necessary adjustments and optimize your queries. Bear in mind that you may need to run EXPLAIN to get your query to an optimal level.

5. Expectations vs. Reality As we work with so many tbl_example, we often see some patterns in the concerns they bring to us. Here’s one of the most common questions we get asked:  Why doesn’t my query use the indexes that we have created?  There is no single answer for why the MySQL optimizer doesn’t use an index. However, one of the main reasons is that the statistics are not up to date.  The good news is that you can refresh the statistics that MySQL optimizer uses by running the following command:  ANALYZE TABLE [table_name];  For example, here’s how you can run it on the tbl_example table: ANALYZE TABLE tbl_example; The image below describes the output of the ANALYZE command on the tbl_example table:

6. A word of caution:  If you are dealing with tables that have millions of rows, the ANALYZE command can lock the table for a short duration. Thus, we recommend you execute this command during low database load times.

7. Here’s a view of the result columns for executing the EXPLAIN PLAN command on the latest release of MySQL is 8.x :

8. Let’s dig into each of the rows you see in the table above: 1. id (JSON name: select_id) The id field is a sequential reference used within the query by MySQL. Observing the EXPLAIN command’s output that has multiple lines will reveal that the output has sequential numbering for the rows. 2. select_type (JSON name: none) The select type field provides the most information compared to others. It contains references about the DML statements like SELECT and it also shows how it will be  interpreted by MySQL. Here are the various values that the select_type options provide:

9. 3. table (JSON name: table_name) This field represents the table’s name that the EXPLAIN plan uses.  4. partitions (JSON name: partitions) If the table that is used by the query has partitions, then this field elaborates on the partition that the query will use. 5. type (JSON name: access_type) The type field explains the type of join that is being used. The type field can represent various types of joins that are possible as described in the following table: 

11. The Extra column of the EXPLAIN tool’s Result The extra column of the EXPLAIN command’s result contains key information on how MySQL will support any given query. Thus,  the information provided by this column makes it very important for our optimization process A Pro Optimization Tip: When you are performing optimizations and trying to make your query run faster, check the information in the EXTRA column. See if you can find messages such as “UsingFilesort and Using Temporary Table”. We will cover how to deal with scenarios where these messages appear later,

12. Next, let’s look at the most important messages you’ll find in the EXPLAIN view: Full scan on NULL key: You get this message when MySQL isn’t able to access an index for a subquery. Impossible HAVING: This message indicates that the HAVING clause isn’t able to select any records. Impossible WHERE: this message indicates that the WHERE clause cannot find records. Not exists: As a rule of thumb, MySQL is capable of optimizing the LEFT JOIN but it cannot evaluate the previous tables. It can only find a single record. Here is an example: SELECT * FROM tbl_example LEFT JOIN tbl_example_history ON tbl_example.id=tbl_example_history.id WHERE t2.id IS NULL; Now, let’s consider that the tbl_example_history.id is defined as NOT NULL.  In this case, MySQL scans the tbl_example table and looks for rows in tbl_example_history using values from the tbl_example.id column. If MySQL finds a matching line in tbl_example_history, it knows that tbl_example_history.id can never be NULL and does not scan the rest of the rows in the tbl_example_history table for the same ID value. 6. Using filesort: MySQL does additional work to find the records in the requested ranking. The rank the records, MySQL browses all the records relative to the join. It will then store the search key and pointer records that are found. Finally, the records that are found will be sorted according to the requested order. 7. Using index: This message is purely used for guidance only and it tells you that MySQL used an index to execute your query.

13. 8. Using index condition: This message indicates that MySQL has used tables and index tuples to read records through an index. 9. Using join buffer: When MySQL uses joins that were previously executed and stores in memory, this message is used. The memory that is used to store the join details is called join_buffer. This is not a desirable message to have because if you do not have enough memory, MySQL will use the disk to compensate for the execution. 10. Using temporary: This message is displayed when either a Group By clause or a Sort By clause has been used. In such scenarios, MySQL will store your data in a temporary table to work with the records. There is another reason why MySQL might have used the temporary table and that is because there was a shortage of memory. If there was a memory shortage, then the RAM requirements of the MySQL server need to be revisited. Summary Through this guide, you have now learned how to effectively read the EXPLAIN plan tool and how to interpret the MySQL explain plan view. You have also got a deeper understanding of the various options and messages that can be shown to you every time you run the EXPLAIN on a query.