Table of Contents for

MySQL in a Nutshell, 2nd Edition

Use this statement to display information about the columns of a given table or the handling of a SELECT statement. For the first usage, the statement is synonymous with the DESCRIBE and SHOW COLUMNS statements. 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. Here is an example 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 of 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

We can discern plenty 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 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 judgment. See Table 6-1 for a list of possible select_types.