Query optimization has a lot to do with the proper use of indexes. The EXPLAIN command examines 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 JOINs. The syntax for EXPLAIN is EXPLAIN SELECT statement The output of the EXPLAIN command is a table of information containing the following columns:
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. 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. Suppose you have 2 and 1 as results; multiply these numbers together, and you have 2 as your answer. This is the number of rows that MySQL must look at 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, visit the MySQL Manual at http://dev.mysql.com/doc/refman/5.0/en/explain.html. |