- Building an Optimized Platform
- MySQL Startup Options
- Optimizing Your Table Structure
- Optimizing Your Queries
- Summary
- Q&A
- Workshop
This chapter is from the book
This chapter is from the book
This chapter is from the book
Optimizing Your Queries
Query optimization has a lot to do with the proper use of indexes. The EXPLAIN command will examine a given SELECT statement to see whether it's optimized the best that it can be, using indexes wherever possible. This is especially useful when looking at complex queries involving JOIN. The syntax for EXPLAIN is
EXPLAIN SELECT statement
The output of the EXPLAIN command is a table of information containing the following columns:
table—The name of the table.
type—The join type, of which there are several.
possible_keys—This column indicates which indexes MySQL could use to find the rows in this table. If the result is NULL, no indexes would help with this query. You should then take a look at your table structure and see whether there are any indexes that you could create that would increase the performance of this query.
key—The key actually used in this query, or NULL if no index was used.
key_len—The length of the key used, if any.
ref—Any columns used with the key to retrieve a result.
rows—The number of rows MySQL must examine to execute the query.
extra—Additional information regarding how MySQL will execute the query. There are several options, such as Using index (an index was used) and Where (a WHERE clause was used).
The following EXPLAIN command output shows a non-optimized query:
mysql> EXPLAIN SELECT * FROM master_name; +-------------+------+---------------+------+---------+------+------+-------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------------+------+---------------+------+---------+------+------+-------+ | master_name | ALL | NULL | NULL | NULL | NULL | 9 | | +-------------+------+---------------+------+---------+------+------+-------+ 1 row in set (0.00 sec)
However, there's not much optimizing you can do with a "select all" query except add a WHERE clause with the primary key. The possible_keys column would then show PRIMARY, and the Extra column would show Where used.
Think back to the example in Hour 11, "Advanced Usage of SELECT Statements," where you used a RIGHT JOIN on the master_name and email tables. Using EXPLAIN, you can see that this is an optimized query:
Figure 20.1 EXPLAIN output for an optimized query.
){kind=link}
In the type column, you see the value eq_ref instead of ALL. When ALL is present, it means that all relevant tables are scanned during the course of the query for each combination of rows used. In other words, everything is looked at! On the other hand, eq_ref means that only one row will be read for each combination of rows; this indicates that indexes are being used properly and that the JOIN has done its job.
When using EXPLAIN on statements involving JOIN, a quick way to gauge the optimization of the query is to look at the values in the rows column. In the previous example, you have 2 and 1. Multiply these numbers together and you have 2 as your answer. This is the number of rows that MySQL must look at in order to produce the results of the query. You want to get this number as low as possible, and 2 is as low as it can go!
For a great deal more information on the EXPLAIN command, please visit the MySQL manual at http://www.mysql.com/doc/E/X/EXPLAIN.html.