MySQL Explain Plan

The MySQL explain plan shows how the MySQL optimizer has decided to run a SELECT statement and access the data. MySQL can only explain SELECT statements.

The command syntax for viewing the EXPLAIN output is:

explain [select statement]

For example:

explain SELECT t0.id AS id1, t0.name AS name2, t0.price AS price3, t0.description AS description4
FROM product t0
WHERE t0.id = 1

The AppDynamics for Databases window displays the explain output for this command:

The columns in the Explain plan table are:

id: The SELECT identifier. This is the sequential number of the SELECT within the query.

select_type: The type of SELECT, which can be any of those shown in the following table:

SIMPLE	Simple SELECT (not using UNION or subqueries)
PRIMARY	Outermost SELECT
UNION	Second or later SELECT statement in a UNION
DEPENDENT UNION	Second or later SELECT statement in a UNION, dependent on outer query
UNION RESULT	Result of a UNION.
SUBQUERY	First SELECT in subquery
DEPENDENT SUBQUERY	First SELECT in subquery, dependent on outer query
DERIVED	Derived table SELECT (subquery in FROM clause)

table: The table to which the row of output refers.
type: The join type.
possible_keys: The possible_keys column indicates which indexes MySQL can choose from use to find the rows in this table.
key: The key column indicates the key (index) that MySQL actually decided to use. The key is NULL if no index was chosen. To force MySQL to use or ignore an index listed in the possible_keys column, use FORCE INDEX, USE INDEX, or IGNORE INDEX in your query.
key_len: The key_len column indicates the length of the key that MySQL decided to use. The length is NULL if the key column says NULL.
ref: The ref column shows which columns or constants are compared to the index named in the key column to select rows from the table.
rows: The rows column indicates the number of rows MySQL believes it must examine to execute the query.
filtered:
Extra: This column contains additional information about how MySQL resolves the query.