Home arrow MySQL arrow Page 2 - Analyzing Queries for Speed with EXPLAIN

13.2.2 How EXPLAIN Works - MySQL

When you are trying to optimize your queries to run quickly and efficiently, you may encounter queries that really should run faster. That's where EXPLAIN comes in handy. This article shows you how to use EXPLAIN in query analysis. It is excerpted from chapter 13 of the MySQL Certification Guide, written by Paul Dubois et al. (Sams, 2005; ISBN: 0672328127).

TABLE OF CONTENTS:
  1. Analyzing Queries for Speed with EXPLAIN
  2. 13.2.2 How EXPLAIN Works
  3. 13.2.3 Analyzing a Query
  4. 13.2.4 EXPLAIN Output Columns
By: Sams Publishing
Rating: starstarstarstarstar / 13
August 10, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More MySQL Articles          >>> More By Sams Publishing
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: