MySQL in a Nutshell, 2nd Edition

Name

EXPLAIN

Synopsis

EXPLAIN table

EXPLAIN [EXTENDED|PARTITIONS] SELECT...

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: ...