Optimizing Your Queries


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:

  • idThe select identifier ID.

  • select_typeThe type of SELECT statement, of which there are several.

  • tableThe name of the table.

  • typeThe join type, of which there are several.

  • possible_keysThis 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 you could create that would increase the performance of this query.

  • keyThe key actually used in this query, or NULL if no index was used.

  • key_lenThe length of the key used, if any.

  • refAny columns used with the key to retrieve a result.

  • rowsThe number of rows MySQL must examine to execute the query.

  • extraAdditional 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).

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.




Sams Teach Yourself PHP, MySQL And Apache All in One
Sams Teach Yourself PHP, MySQL and Apache All in One (3rd Edition)
ISBN: 0672328739
EAN: 2147483647
Year: 2004
Pages: 327

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net