MySQueaL Resources

EXPLAIN Statement

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.

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.

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.