Analyzing Queries for Speed with EXPLAIN - 13.2.2 How EXPLAIN Works (Page 2 of 4 )
To use EXPLAIN, write your SELECT query as you normally would, but place the keyword EXPLAIN in front of it. As a very simple example, take the following statement:
SELECT 1;
To see what EXPLAIN will do with it, issue the statement like this:
mysql> EXPLAIN SELECT 1;
+----------------+
| Comment |
+----------------+
| No tables used |
+----------------+
In practice, it's unlikely that you'd use EXPLAIN very often for a query like that because the output doesn't tell you anything particularly interesting. However, the example does illustrate an important principle: EXPLAIN can be applied to any SELECT query. One of the implications of this principle is that you can use EXPLAIN with simple queries while you're learning how to use it and how to interpret its results. You don't have to begin with a complicated multiple-table join.
With that in mind, consider these two simple single-table queries:
SELECT * FROM Country WHERE Name = 'France';
SELECT * FROM Country WHERE Code = 'FRA';
Both queries produce the same output (information about the country of France), but they are not equally efficient. How do you know? Because EXPLAIN tells you so. When you use EXPLAIN with each of the two queries, it provides the following information about how the MySQL optimizer views them:
mysql> EXPLAIN SELECT * FROM Country WHERE Name =
'France'\G
********************* 1. row ***************************
table: Country
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 239
Extra: Using where
mysql> EXPLAIN SELECT * FROM Country WHERE Code =
'FRA'\G
********************* 1. row ***************************
table: Country
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 3
ref: const
rows: 1
Extra:
EXPLAIN produces several columns of information. In the example just shown, NULL in the possible_keys and key columns shows for the first query that no index is considered available or usable for processing the query. For the second query, the table's PRIMARY KEY column (the Code column that contains three-letter country codes) can be used, and is in fact the one that the optimizer would choose. The rows column of the EXPLAIN output shows the effect of this difference. Its value indicates the number of rows that MySQL estimates it will need to examine while processing the query:
For the first query, the value is 239, which happens to be the number of rows in the Country table. This value indicates that MySQL would scan all rows of the table, which is inefficient.
For the second query, only one row need be examined. This is because MySQL can use the table's primary key to go directly to the single relevant row.
This example briefly indicates the kind of useful information that EXPLAIN can provide, even for simple queries. The conclusion to draw is that, if possible, you should use the Code column rather than the Name column to look up Country table records. However, the real power of EXPLAIN lies in what it can tell you about joins—SELECT queries that use multiple tables.
EXPLAIN is especially important for join analysis because they have such enormous potential to increase the amount of processing the server must do. If you select from a table with a thousand rows, the server might need to scan all one thousand rows in the worst case. But if you perform a join between two tables with a thousand rows each, the server might need to examine every possible combination of rows, which is one million combinations. That's a much worse worst case. EXPLAIN can help you reduce the work the server must do to process such a query, so it's well worth using.
Next: 13.2.3 Analyzing a Query >>
More MySQL Articles
More By Sams Publishing
|
 This article is excerpted from chapter 13 of the MySQL Certification Guide, written by Paul Dubois et al. (Sams, 2005; ISBN: 0672328127). Check it out today at your favorite bookstore. Buy this book now.
|
|
/ 11 
