Managing & Using MySQL, 2nd Edition

Name

EXPLAIN

Syntax

EXPLAIN [table_name | sql_statement]

Description

Used with a table name, this command is an alias for SHOW COLUMNS FROM table_name.

Used with an SQL statement, this command displays verbose information about the order and structure of a SELECT statement. This can be used to see where keys are not being used efficiently. This information is returned as a result set with the following columns:

table

The name of the table referenced by the result set row explaining the query.

type

The type of join that will be performed. See Chapter 5 for an explanation of the output.

possible_keys

Indicates which indexes MySQL could use to build the join. If this column is empty, there are no relevant indexes and you should probably build some to enhance performance.

key

Indicates which index MySQL decided to use.

key_len

Provides the length of the key MySQL decided to use for the join.

ref

Describes which columns or constants were used with the key to build the join.

rows

Indicates the number of rows MySQL estimates it will need to examine to perform the query.

Extra

Additional information indicating how MySQL will perform the query. See Chapter 5 for an explanation of the output.

Example

EXPLAIN SELECT customer.name, product.name FROM customer, product, purchases 
WHERE purchases.customer=customer.id AND purchases.product=product.id