Specifications

Using MySQL
P
ART II
258
+-------------+--------+---------------+---------+---------+------------------+------+-------------+
| table | type | possible_keys | key | key_len | ref |rows | Extra |
+-------------+--------+---------------+---------+---------+------------------+------+-------------+
| orders | ALL | PRIMARY | NULL | NULL | NULL | 4 | |
| order_items | ref | PRIMARY | PRIMARY | 4 | orders.orderid | 1 | Using index |
| customers | ALL | PRIMARY | NULL | NULL | NULL | 3 | where used |
| books | eq_ref | PRIMARY | PRIMARY | 13 | order_items.isbn | 1 | where used |
+-------------+--------+---------------+---------+---------+------------------+------+-------------+
FIGURE 11.2
The output of the EXPLAIN statement.
This might look confusing at first, but it can be very useful. Let’s look at the columns in this
table one by one.
The first column, table, just lists the tables used to answer the query. Each row in the result
gives more information about how that particular table is used in this query. In this case, you
can see that the tables used are orders, order_items, customers, and books. (We knew this
already by looking at the query.)
The type column explains how the table is being used in joins in the query. The set of values
this column can have is shown in Table 11.7. These values are listed in order from fastest to
slowest in terms of query execution. It gives you an idea of how many rows need to be read
from each table in order to execute a query.
TABLE 11.7 Possible Join Types as Shown in Output from EXPLAIN
Type Description
const or system The table is read from only once. This happens when the table
has exactly one row. The type system is used when it is a system
table, and the type const otherwise.
eq_ref For every set of rows from the other tables in the join, we read
one row from this table. This is used when the join uses all the
parts of the index on the table, and the index is UNIQUE or is
the primary key.
ref For every set of rows from the other tables in the join, we read a
set of rows from this table which all match. This is used when
the join cannot choose a single row based on the join condition,
that is, when only part of the key is used in the join, or if it is not
UNIQUE or a primary key.
range For every set of rows from the other tables in the join, we read a
set of rows from this table that fall into a particular range.
index The entire index is scanned.
ALL Every row in the table is scanned.
14 7842 CH11 3/6/01 3:35 PM Page 258