Decoding MySQL EXPLAIN Query Results for Better Performance

MySQL is one of the most used RDMS. We write different types of queries in MySQL. Some used to be simple, and some queries used to be complex that includes even more than 4 - 5 tables. When we write complex queries, understanding the effect of the query is essential.

Sometimes poorly written complex queries can be the reason for a database's poor performance. We can use EXPLAIN to find the keys used in the query and how we can optimize them.

This blog is about how we can get useful information from the result of EXPLAIN.

The output of ‘explain’ contains information on each table used in the query and in the order in which MySQL read the tables while processing the query. It means that MySQL reads the data from the table present in the first row, then finds the matching row in the table present in the second row, and so on.

Columns present the EXPLAIN output are id, select_type, table, partitions, possible_keys, key, key_len, ref, rows, filtered, type, and extra.

select_type: There are different types of select. This column shows the types of select. The various types of selects are SIMPLE, UNION, SUBQUERY, and DERIVED.

1.1 SIMPLE: Select is simple. It means no sub-query or union is used here.

1.2 Subquery: Any select query used in another select query but not in the FROM clause. For example, SELECT * FROM student_detail where address_key in (SELECT id from address where state = 'Karnataka');

1.3 DERIVED: SELECT query used in the FROM clause of another SELECT query is a DERIVED select_type. For example,

SELECT id FROM (SELECT * FROM address) ad;

1.4 UNION: Second SELECT query in UNION or subsequent SELECT query type is a UNION select_type. For example,

SELECT state FROM address where id = 2 UNION SELECT state from address where id = 4 UNION SELECT state from address where id = 3;

table: Name of the table. It can be the table name or the name provided by us during a query or <derivedn>(derived table in the row with id vale n).

partitioned: Partition of the table from which the data is fetched. If the table is not partitioned then it shows a NULL value, or it may be possible that this column is not present in the output result if none of the tables is partitioned.

possible_keys: It shows all the possible indexes from which MySQL can use the index to find the rows in the table. But this key is not dependent on the order of the table in the output result i.e it does not depend on the order of the table in which MySQL process the query. So it may be possible that none of the keys is used. I have explained it below.

key: It shows the actual key that is used to find the rows in the table. Normally this key used to be one of the keys present in possible_keys, but it can be a different key too. For example, If it is optimized to fetch all the required columns from an index directly using the index scan then MySQL can use the index scan instead of the data row scan.

key_len: It shows the length of the key used to get the data. If the key is a multi-part key then key_len helps us to find how many parts of the multi_part key are used to get the data.

ref: It indicates which column is used against the column present in the key to fetch the data from the table. It can be const too i.e a const value is used to compare. For example, SELECT * FROM student_detail sd INNER JOIN address ad ON sd.address_key = ad.id WHERE sd.id = 2. In this query, the key used in the JOIN clause is also present in the where clause and it means that we are fetching a fixed number of rows using that key from the table.

rows: It indicated the approx(not actual) number of rows need to examine.

filtered: The filtered column indicates an estimated percentage of table rows filtered by the table condition. The maximum value is 100, which means no filtering of rows occurred. Values decreasing from 100 indicate increasing amounts of filtering. rows show the estimated number of rows examined and rows × filtered shows the number of rows joined with the following table. For example, if the row is 1000 and the filtered is 50.00 (50%), the number of rows to be joined with the following table is 1000 × 50% = 500. You can check the official document MySQL.

I will explain the type and extra column usage in the next blog. Both required detailed explanations and one blog is not enough for this.

Also published here.