Database Reference
In-Depth Information
gives the same results as describing a table as follows:
DESCRIBE tablename
EXPLAIN comes into its own when you apply it to a query as we have done in Figure
13.2. Simply add the keyword EXPLAIN in front of the query that you wish to analyse.
When we EXPLAIN our three table join query we get the results shown in Figure 13.3.
When you EXPLAIN a query, the MySQL server does not actually run the query, but
gives an approximation of what it would have to do were you to run it. This means that if
you were about to run a query on a huge amount of data, you could EXPLAIN the query first
to make sure that it would run in the best way.
The first column of the EXPLAIN, table , shows the tables that the query will have to ref-
erence to show the results. In this case it is three tables, webpage, log and cookies. Of course
we do not actually need to use EXPLAIN to tell us this, but the rest of the columns are of
more use!
The second column, type , signifies the type of join that MySQL will have to do to process
the row. The log table will use the ALL join, and the webpage and cookies tables will use the
eq_ref join. The full set of joins that can appear here are shown in Table 13.1. This table runs
from the slowest and most process-intensive join to the fastest, most efficient one.
The third column in the EXPLAIN output, possible_keys , shows the keys that MySQL
hopes to use to execute the query. This column is called possible because the query might
Table 13.1
Join types used by the EXPLAIN facility.
Join type
Explanation
ALL
The query will have to scan all rows of the table to complete this query.
index
The index of this table is used to complete this query.
range
An index is used to select a range of rows from this table for scanning by the query.
ref
If the join cannot match one unique row, a set of rows from this table is read for every set of rows that
match the join condition of the other tables.
eq_ref
One row from this table is read for every set of rows from the other tables when a single row can be
matched by the join.
const
If the table only has one row, it is only read from once.
system
If the table only has one row and is read from once and is also a system table, this join type is noted by
the EXPLAIN.
Figure 13.3
Sample explain results.
 
Search WWH ::




Custom Search