AppDynamics for Databases Knowledge Base Article

Looking at the MySQL Explain Plan

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

The command syntax for viewing the EXPLAIN output is:

explain [select statement]

e.g. explain select count(*) from statistics where id = 5
Here we can see the AppDynamics for Databases screen displaying 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_keysThe possible_keys column indicates which indexes MySQL can choose from use to find the rows in this table.
  • KeyThe 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_lenThe key_len column indicates the length of the key that MySQL decided to use. The length is NULL if the key column says NULL
  • refThe ref column shows which columns or constants are compared to the index named in the key column to select rows from the table.
  • rowsThe rows column indicates the number of rows MySQL believes it must examine to execute the query.
  • Extra This column contains additional information about how MySQL resolves the query

Next Step - Improve Your Database Performance now with AppDynamics for Databases: REQUEST A DEMO START A FREE TRIAL