EXPLAIN

Use this MySQL statement to display information about the columns of a given table or the handling of a SELECT statement. It used to be similar to DESCRIBE and SHOW COLUMNS statements. Now it shows which index the statement will use and, when multiple tables are queried, the order in which the tables are used. It can be helpful in determining the cause of a slow query.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

EXPLAIN table

EXPLAIN [EXTENDED|PARTITIONS] SELECT...

Explanation

Use this MySQL statement to display information about the columns of a given table in MySQL or the handling of a SELECT statement. For the first usage, the statement is synonymous with the MySQL statements, DESCRIBE and SHOW COLUMNS. For the latter usage, EXPLAIN shows which index the statement will use and, when multiple tables are queried, the order in which the tables are used. This can be helpful in determining the cause of a slow query.

The EXTENDED flag sets the output to include the filtered column, which is not otherwise displayed. This provides an estimate of the percentage of table rows the table condition will filter. It also generates additional information available through a SHOW WARNINGS statement.

The PARTITIONS flag sets the output to include the partitions column, which is not otherwise displayed. In the event of a partitioned table, this provides information as to which partitions the SELECT query calls upon.

Examples

An example of this MySQL statement follows involving a simple subquery in which we are retrieving a list of our top clients and counting the number of work request tickets they've generated, and then querying those results to order them by the number tickets.

EXPLAIN
SELECT * FROM
   (SELECT client_name, COUNT(*) AS tickets
    FROM work_req 
    JOIN clients USING(client_id) 
    WHERE client_type = 1 
    AND DATEDIFF(NOW(), request_date) < 91
    GROUP BY client_id) AS derived1
    ORDER BY tickets DESC;

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 8
        Extra: Using filesort
*************************** 2. row ***************************
           id: 2
  select_type: DERIVED
        table: clients
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 94
        Extra: Using where; Using temporary; Using filesort
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: work_req
         type: ref
possible_keys: client_id,workreq_date_key
          key: workreq_date_key
      key_len: 5
          ref: company_database.clients.client_id
         rows: 1
        Extra: Using where; Using index

Plenty can be discerned from these results, such as which indexes were used, if any. For example, the possible_keys field in the third row lists the indexes that might have been be used to find the data, whereas the key field indicates that the index workreq_date_key was actually used. (That index covers the client_id and request_date columns.) If the possible_keys field showed a value of NULL, then no index was used or could have been used. This would indicate that you should consider adding an index to the table.

Basically, this statement tells you what MySQL does when it executes the given SQL statement. It doesn't tell you what to do differently to improve performance. For that, you will need to use your judgement.

  • SIMPLE

    Indicates a simple SELECT statement, without a subquery or a UNION.

  • PRIMARY

    When using a subquery, this is the main SELECT statement.

  • UNION

    When using a UNION, this is not the first SELECT statement.

  • DEPENDENT UNION

    When using a UNION, this is not the first SELECT statement which is dependent on the main query

  • UNION RESULT

    The result of a UNION.

  • SUBQUERY

    The first SELECT statement in a subquery.

  • DEPENDENT SUBQUERY

    The first SELECT statement in a subquery which is dependent on the main query.

  • DERIVED

    The table derived from the subquery.

  • UNCACHEABLE SUBQUERY

    Indicates a subquery in which the results cannot be cached and therefore must be re-evaluated for each row of the main query.

  • UNCACHEABLE UNION

    The UNION of a subquery in which the results cannot be cached and therefore must be re-evaluated for each row of the main query.