Explaining SQL Explain

MySQL and MariaDB provide a useful EXPLAIN command which analyzes queries and detect potential performance issues. EXPLAIN describes how a SELECT will be processed including information about JOINS. EXPLAIN EXTENDED provides additional information and estimates the number of table rows that are filtered by the condition. Everyone uses this frequently. Now- how to interpret a query’s explain plan-

EXPLAIN SELECT * FROM user;
+------+-------------+-------+------+---------------+------+---------+------+--------+-------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra |
+------+-------------+-------+------+---------------+------+---------+------+--------+-------+
|    1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 123456 |       |
+------+-------------+-------+------+---------------+------+---------+------+--------+-------+
1 row in set (0.00 sec)
id

A sequential identifier for each SELECT within the query e.g.

EXPLAIN SELECT * FROM user where currency_id in (select id from currency);
+------+-------------+----------+--------+---------------+---------+---------+--------------------------+--------+-------------+
| id   | select_type | table    | type   | possible_keys | key     | key_len | ref                      | rows   | Extra       |
+------+-------------+----------+--------+---------------+---------+---------+--------------------------+--------+-------------+
|    1 | PRIMARY     | user     | ALL    | NULL          | NULL    | NULL    | NULL                     | 123456 | Using where |
|    1 | PRIMARY     | currency | eq_ref | PRIMARY       | PRIMARY | 4       | dbname.user.currency_id  |     1  | Using index |
+------+-------------+----------+--------+---------------+---------+---------+--------------------------+--------+-------------+
2 rows in set (0.00 sec)
select_type

Different types. Possible values could be-

    SIMPLE: Simple SELECT query without any subqueries or UNION
    PRIMARY: SELECT is in the outermost query in a JOIN or Subquery
    DERIVED: SELECT is part of a subquery within a FROM clause
    SUBQUERY: First SELECT in subquery
    DEPENDENT SUBQUERY: A subquery which is dependent upon on outer query
    UNCACHEABLE SUBQUERY: A subquery which is not cacheable
    UNION: SELECT is the second or later statement of a UNION
    DEPENDENT UNION: Second or later SELECT of a UNION is dependent on an outer query
    UNION RESULT: SELECT is a result of a UNION
table

Self explanatory

type

Important one! It indicates missing indexes or how the query is written should be reconsidered. Possible values could be-

    system: Table has only zero or one row
    const: Table has only one matching row which is indexed. Constant time; This is the fastest type of join e.g.

    EXPLAIN SELECT * FROM user WHERE id = 12345;
    +------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
    | id   | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
    +------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
    |    1 | SIMPLE      | user  | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
    +------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
    
    eq_ref: All parts of an index are used by the join and the index is PRIMARY KEY or UNIQUE NOT NULL. This is the next best possible join type.
    ref: All of the matching rows of an indexed column are read for each combination of rows from the previous table. This type of join appears for indexed columns compared using = or operators.
    fulltext: The join uses the table’s FULLTEXT index.
    ref_or_null: Same like ref but also contains rows with a null value for the column.
    index_merge: The join uses a list of indexes to produce the result set. The key column of EXPLAIN‘s output will contain the keys used.
    unique_subquery: IN subquery returns only one result from the table and makes use of the primary key.
    index_subquery: Same as unique_subquery but returns more than one result row.
    range: An index is used to find matching rows in a specific range, typically when the key column is compared to a constant using operators like BETWEEN, IN, >, >= etc.
    index: The entire index tree is scanned to find matching rows.
possible_keys

Possible keys that can be used by MySQL to find data from the table. This column helps to optimize a query because if the column is NULL, it indicates no relevant indexes could be found.

key

The actual index used by MySQL. May contain an index that is not listed in the possible_key column. MySQL optimizer always look for an optimal key e.g. While joining many tables, it may figure out some other keys which is not listed in possible_key but are more optimal.

key_len

Indicates the length of the index the Query Optimizer chose to use. For example, a key_len value of 4 means it requires memory to store four characters.

ref

Shows the columns or constants that are compared to the index named in the key column. MySQL will either pick a constant value to be compared or a column itself based on the query execution plan.

rows

Total number of records that were examined to produce the output.

Extra

Contains additional information regarding the query execution plan. Values such as “Using temporary”, “Using filesort” etc. in this column may indicate a troublesome query. Here is the complete list

Also it’s worth to check Explain Analyzer. Easy to use and explains what’s going on in plain English e.g. Selection_006

[Source: EXPLAIN Output Format, Using EXPLAIN to Write Better MySQL Queries and How to Explain Your SQL EXPLAIN Queries]

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s