Chapter 13. Optimizing for Query Speed

This chapter discusses general principles that are useful for optimizing queries to run more efficiently. It covers the following optimization strategies:

• The primary optimization technique for reducing lookup times is to create good indexes. This is true not just for retrievals (SELECT statements); indexing reduces row lookup time for UPDATE and DELETE statements as well. You should know general principles for creating useful indexes and for avoiding unnecessary ones.

• The EXPLAIN statement provides information about how the MySQL optimizer processes queries. This is of value when you're trying to determine how to make a query run better (for example, if you suspect indexes are not being used as you think they should be).

• The way a query is written might prevent indexes from being used even if they are available. Rewriting the query often will allow the optimizer to use an index and process a query faster. Other times you can use query modifiers to give the scheduler a hint about how to execute a query.

• In some cases, query processing for a task can be improved by using a different database design. This includes techniques such as choosing a storage engine with properties that best match application requirements and using summary tables.

Questions on the material in this chapter make up approximately 15% of the exam.

Why be concerned about optimization? The most obvious reason is to make your queries run faster. Another is that optimizing your queries helps everybody who uses the server, not just you. When the server runs more smoothly and does more with less work, it performs better as a whole:

• A query that takes less time to run doesn't hold locks as long. Other clients that are waiting to update a table have to wait less time for a fast query than a slow one. This reduces the chance of a query backlog building up.

• A query might be slow because it does not use indexes and therefore MySQL must scan a table in its entirety. For a large table, that involves a lot of processing and disk activity. This extra overhead affects not only your own query, it takes machine resources that could be devoted to processing other queries. Adding effective indexes allows MySQL to read only the relevant parts of the table, which is quicker and less disk intensive.

The optimization strategies covered here are guidelines known to result in generally improved query performance. However, you must test them in specific circumstances and measure the results, particularly if you can choose from more than one technique in a given situation.

The techniques discussed in this chapter can be used by any client application to improve how the queries it issues are executed by the server. Another approach to performance improvement is to reconfigure the server itself to change its overall operation. Server tuning is addressed in Chapter 16, "Advanced Server Features."