MySQL EXPLAIN
The EXPLAIN keyword is used to obtain information about how our SQL databases execute the queries in MySQL. It is synonyms to the DESCRIBE statement. In practice, the DESCRIBE keyword provides table structure information, whereas the EXPLAIN keyword gives the query execution plan. It is a powerful tool to understand and optimize the queries in MySQL, but developers used it very rarely.
It can work with INSERT, SELECT, DELETE, UPDATE, and REPLACE queries. From MySQL 8.0.19 and later versions, it can also work with TABLE statements. When we use this keyword in queries, it will process the statement and provide the information about each step in the execution plan, such as how tables are joined, the table’s order, estimated partitions, etc. It returns single or multiple rows that explain each part of the execution plan and the order of execution.
Let us understand it with the help of an example.
Example
Suppose we have a table named “student_info” and “orders” in the sample database that contains the following data:
If we want to show the execution plan of a SELECT statement, we can use the query as below:
Output:
This query produces the following information:
Sometimes we do not want to scan the whole table. In that case, we need to use the INDEX to see the information based on a specified condition. Execute the below statement to create an index in the student_info table:
If the index is created successfully, it will produce the below output:
Now, execute the following query to avoid the complete table scan in the database:
It will produce the output as below image:
Output:
After execution, we will get the output like the below image:
In the above output, we can see that the select_type is SIMPLE for both tables. Both tables follow a one-to-many relationship. The student_info table’s primary key is used as a foreign key to the orders table. Therefore, the possible_keys value for the second row is order_id. The filtered value in the first row is 12.50% for the student_info table because ‘Barack’ is the fourth entry of this table. The filtered value in the second row is 100% for the orders table. It is because all values of the orders table must be checked to retrieve the data.
EXPLAIN Keyword in SELECT Query with UNION ALL Operator
UNION ALL is an operator that returns all matching column values with duplicates from the related tables while using the SELECT query. The below statement shows the EXPLAIN output by using the UNION ALL operator between student_info and orders tables.
Output:
It will produce the below output where we can see that the value of select_type is UNION for the second row and the value of the Extra column is the index:
MySQL EXPLAIN Keyword Limitations
The following are the most common limitations of the EXPLAIN keyword in MySQL:
- EXPLAIN does not provide any information about how triggers, stored functions, or UDFs will affect our query.
- The EXPLAIN keyword cannot work for stored procedures.
- It doesn’t tell you about optimization MySQL does during query execution.
- It produces the estimated statistics that can be very inaccurate.
- It doesn’t produce every information regarding a query’s execution plan.
MySQL EXPLAIN ANALYZE
It is a profiling tool for queries that shows information about where MySQL spends time on query execution 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, it will print the plan and measurement instead of the query result.
Example:
Output:
