Optimizing MySQL - Database Design Manual: Using MySQL for Windows - page 150

not actually use all of the keys in the search. The keys are independent of the order of the
tables, so some keys may never actually be processed. If one of these columns is NULL, you
may be able to optimize the query further by adding an index to the table. We will do that
later in the section on indexes.
The fourth column in the EXPLAIN output, key , shows the key that MySQL actually used
to perform the query. If no key is used then the entry is NULL. If there is a PRIMARY entry
for a column in the possible_keys column which is NULL in this column then there may be
a way of optimizing the query further. The optimize table section will show how to do this.
The fifth column in the EXPLAIN output, key_len , gives us the length of the key that was
used. You can use this column to see if MySQL is using more than one column when a mul-
tiple column key exists.
The sixth column in the EXPLAIN output, ref , shows which columns were used by the
key join. You will notice from Figure 13.3 that the log row contains a NULL for this column,
showing that no key was used for this part of the join.
The seventh column in the EXPLAIN output, rows , shows the estimated number of rows
that the query will use to fulfil the join. This is not necessarily the number of rows that the
query will return, but the number that the query will need to look at to execute. If you mul-
tiply all of the row values together, you get an estimate of the total number of rows exam-
ined by the query. The next section will examine this further.
The last column in the EXPLAIN output, extra , gives you any extra information about
how the join will be carried out. Table 13.2 shows what you might find in this column.

Table 13.2 Possible entries in the extra column.

Entry Explanation

Distinct As soon as the first match is found no more searching will be done within this table.

Not exists An optimized LEFT JOIN will be used and as soon as a matching column is found no

more rows will be examined in the table.

Range checked for each record MySQL tries to find an index for each row in the set of rows from the preceding tables

and uses this index if available to obtain the rows.

Using filesort This takes two passes as it has to sort the rows to retrieve them in the second pass but

needs to work out how to sort these rows out in the first pass.

Using index The index alone is used to retreive the required join data without any of the rows being

scanned.

Using temporary The query needs to create a temporary table to hold the results while processing. If you

do a GROUP BY on a different set of columns than an ORDER BY this will occur.

Using where A where clause is used to select the rows required by the query.

Looking at Cross-joins Again

You may remember that in Chapter 7 we talked about performing a cross join which pro-
duced a lot of rows from tables with only a few rows. We can demonstrate this by running
the following:

SELECT *
FROM log, cookies, webpage, visitorbook