Specifications

In the previous example, you can see that one of the tables is joined using eq_ref (books), and
one is joined using ref (order_items), but the other two (orders and customers) are joined
by using ALL; that is, by looking at every single row in the table.
The rows column backs this up—it lists (roughly) the number of rows of each table that has to
be scanned to perform the join. You can multiply these together to get the total number of rows
examined when a query is performed. We multiply these numbers because a join is like a prod-
uct of rows in different tables—check out Chapter 9, “Working with Your MySQL Database,”
for details. Remember that this is the number of rows examined, not the number of rows
returned, and that it is only an estimate—MySQL can’t know the exact number without per-
forming the query.
Obviously, the smaller we can make this number, the better. At present we have a pretty negli-
gible amount of data in the database, but when the database starts to increase in size, this query
would blow out in execution time. We’ll return to this in a minute.
The possible_keys column lists, as you might expect, the keys that MySQL might use to join
the table. In this case, you can see that the possible keys are all PRIMARY keys.
The key column is either the key from the table MySQL actually used, or NULL if no key was
used. You’ll notice that, although there are possible PRIMARY keys for the orders and
customers tables, they were not used in this query. We’ll look at how to fix this in a minute.
The key_len column indicates the length of the key used. You can use this to tell whether only
part of a key was used. This is relevant when you have keys that consist of more than one col-
umn. In this case, where the keys were used (order_items and books), the full key was used.
The ref column shows the columns used with the key to select rows from the table.
Finally, the Extra column tells you any other information about how the join was performed.
The possible values you might see in this column are shown in Table 11.8.
T
ABLE 11.8 Possible Values for Extra Column as Shown in Output from EXPLAIN
Value Meaning
Not exists The query has been optimized to use LEFT JOIN.
Range checked for For each row in the set of rows from the other tables in the join,
each record try to find the best index to use, if any.
Using filesort Two passes will be required to sort the data. (This obviously takes
twice as long.)
Using index All information from the table comes from the index—that is, the
rows are not actually looked up.
Using temporary A temporary table will need to be created to execute this query.
WHERE used A WHERE clause is being used to select rows.
Advanced MySQL
C
HAPTER 11
11
ADVANCED
MY
SQL
259
14 7842 CH11 3/6/01 3:35 PM Page 259