paint-brush
Decoding MySQL EXPLAIN Query Results for Better Performance (Part 2)by@mamit
254 reads

Decoding MySQL EXPLAIN Query Results for Better Performance (Part 2)

by Amit TiwaryMarch 10th, 2023
Read on Terminal Reader
Read this story w/o Javascript
tldt arrow

Too Long; Didn't Read

I used two tables student_details and address table. I have attached the table structure and respective index structure at the end of this blog. I provided the information for all the columns except **type** and **extra**. The type** column provides information about how the tables are joined. Different types of the ** type** are described below.
featured image - Decoding MySQL EXPLAIN Query Results for Better Performance (Part 2)
Amit Tiwary HackerNoon profile picture


In my last blog, I wrote about how to read the output of the explain query in MySQL. I provided the information for all the columns except type and extra. In this blog post, I will cover the type column.


  • type column provides information about how the tables are joined. Different types of the type are described below.

I used two tables student_details and address table. I have attached the table structure and respective index structure at the end of this blog.


  • const: If the type is const it means that there is at most one matching row. Since the number of matched rows is maximum one, it is treated as constant. When we used all the primary keys (if the primary key is a composite primary key) or all the ‘unique’ indexes to compare to a constant value to query data then the type is const. For example, in the below query, I used both the unique index(class, roll_no) to query the data.


SELECT * FROM student_detail sd INNER JOIN 
address ad ON sd.address_key = ad.id 
WHERE roll_no = 12 and class = 4;


const type



eq_ref: It shows that one row is fetched from this table for each combination of rows of the previous table. If all the parts of the primary index or the unique not null index are used to fetch the data then the type is eq_ref.



SELECT * FROM student_detail sd INNER JOIN 
address ad ON sd.address_key = ad.id 
WHERE roll_no = 12;


eq ref


Here all the rows of the student_detail tables are scanned because roll_no is not indexed (class, roll_no is indexed but not only roll_no. MySql can use class or class and roll_no for the index but not only roll_no). There is only one row in the address table for each combination in the student_detail table(We used the id of address to join and it is the primary key in the address table).



ref: If there are multiple rows in the table for each combination of rows of the previous table then the type is ref. If the index is not a primary index or not a unique index or the leftmost prefix of the index is used, that means it is not possible to get the single row for each column, then the type is ref.


SELECT * FROM student_detail sd INNER JOIN 
address ad ON sd.address_key = ad.id 
WHERE state = 'Karnataka'; 


ref example 1


the state is not a primary or unique index, so for address table type is ref because there can be multiple rows for each state.


one more example


EXPLAIN SELECT * FROM address ad WHERE ad.state = 'Karnataka';


ref example 2


index_merge: Sometimes MySQL may choose to merge the rows fetched after multiple range queries on the same table. This is shown as index_merge in the type.


range: If only the rows mentioned in the range are retrieved using the index then it appears as the range in the type.


index: MySQL saves all the indexes separately in storage. Sometimes for the query, only the index need to be scanned and it satisfies all the requirement.


  • If the index is a covering index for the queries and can be used to satisfy all data required from the table, only the index tree is scanned. In this case, the Extra column says Using index. An index-only scan usually is faster than ALL because the size of the index usually is smaller than the table data.


  • A full table scan is performed using reads from the index to look up data rows in index order. Using index does not appear in the Extra column.


EXPLAIN SELECT class FROM student_detail;


index

Here we query only the class column. It is indexed in the student_detail table and directly fetched from the index instead of going to the table and fetching the data.


ALL: A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked const, and usually very bad in all other cases. Normally, you can avoid ALL by adding indexes that enable row retrieval from the table based on constant values or column values from earlier tables.


EXPLAIN is a powerful tool for optimizing MySQL queries. By understanding the output of EXPLAIN, you can identify performance issues and make changes to improve query speed.

In my next blog, I will cover how to use the output of EXPLAIN to optimize the query.


Note:

student_detail table structure

student_detail

student_details indexes

student_detail indexes

address table structure

address table

address table indexes

address table index



Also published here.