Understanding MySQL EXPLAIN output

#mysql #sql #database

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 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 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;

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;

Here all the rows of the student_detail tables is 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 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 primary key in 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';

state is not 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';

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;

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 table and fetch 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 EXPALIN to optimize the query.

Note:
student_detail table structure