| There are several optimization strategies that you can take advantage of to make your queries run faster: The primary optimization technique for reducing lookup times is to use indexing properly. This is true for retrievals (SELECT statements), and indexing also 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 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. 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). In some cases, query processing for a task can be improved by using a different approach to the problem. This includes techniques such as generating summary tables rather than selecting from the raw data repeatedly. Queries run more efficiently when you choose a storage engine with properties that best match application requirements.
Why be concerned about optimization? The most obvious reason is to reduce query execution time. Another is that optimizing your queries helps everybody who uses the server, not just you. When the server runs more smoothly and processes more queries with less work, it performs better as a whole: A query that takes less time to run doesn't hold locks as long, so other clients that are trying to update a table don't have to wait as long. This reduces the chance of a query backlog building up. A query might be slow due to lack of proper indexing. If MySQL cannot find a suitable index to use, it 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 guidelines discussed in this chapter can be used by any client application to improve how the queries it issues are executed by the server. Further discussion of optimization techniques is provided in Chapter 37, "Optimizing Queries." Another approach to performance improvement is to reconfigure the server itself to change its overall operation. Server tuning is addressed in Chapter 39, "Optimizing the Server." |
