37.1. Identifying Candidates for Query Analysis

Much of this chapter is devoted to the use of EXPLAIN as an analysis tool for making queries run faster, but we'll begin with a general discussion of how to identify which queries need optimizing. To this end, you can use several sources of information:

• Use your experience with the performance of individual applications.

• Use the information in the server's log files.

• Use the SHOW PROCESSLIST statement.

The process of identifying which queries to analyze can take into account individual queries and also global information about the query load processed by your MySQL server. At the level of individual queries, you may have some strong suspicions about the need for optimization when a query that you issue (for example, from within a particular application or using the mysql client) clearly takes a long time.

At a more global level, the server's log files can be helpful for getting an overall picture of the types of queries that your server is being used to process:

• The general query log contains a record of all SQL statements received by the server. It is the most representative log in terms of what the server's query mix is. Examining this log can quickly give you an idea of what the server is doing, such as what the typical SELECT statements are. The perspective provided by this log can be especially useful if you are analyzing the performance of a server that you do not normally use and are unfamiliar with.

• The binary log records all statements that modify data. These don't return rows to clients, but they may be using WHERE clauses to determine which rows to modify. The WHERE clauses provide you with clues about where indexing would be helpful for processing updates more quickly.

• The slow query log records queries that take a log time to execute. A query that appears consistently in this log each time it's issued is likely to warrant some attention. Concentrate on those queries that appear in this log most often because it's not likely to be productive to focus on a query that appears in the log only once.

More information about the logs and how to interpret their contents is given in Chapter 24, "Starting, Stopping, and Configuring MySQL," and Chapter 40, "Interpreting Diagnostic Messages."

Another global tool that provides information about query execution is the SHOW PROCESSLIST statement. Use it periodically to get information about what queries currently are running. If you notice that a particular query often seems to be causing a backlog by making other queries block, see whether you can optimize it. If you're successful, it will alleviate the backlog. To get the most information from SHOW PROCESSLIST, you should have the PROCESS privilege. Then the statement will display queries being run by all clients, not just your own queries.

To some extent, "slow" can be a relative term. You don't want to waste time trying to optimize a query that seems slow but is so only for external reasons and is not inherently slow. Queries in the slow log are determined to be slow using wallclock (elapsed) time. Queries will appear more often in the log when the server host is heavily loaded than when it is not, so you should evaluate query execution time against general system activity on that host. A query might appear slow if the machine is very busy, but otherwise perform acceptably. For example, if filesystem backups are taking place, they'll incur heavy disk activity that impedes the performance of other programs, including the MySQL server. The machine might be processing a heavy load for other reasons, such as if you have a very active Web server running on the same host.

Keeping in mind the preceding considerations, you have a good indicator that a query might be in need of optimization if you find that it is consistently slow in comparison to other queries no matter when you run it, and you know the machine isn't just generally bogged down all the time.

Another factor to recognize is that the mere presence of a query in the slow query log does not necessarily mean that the query is slow. If the server is run with the --log-queries-not-using-indexes option, the slow query log also will contain queries that execute without using any index. In some cases, such a query may indeed be a prime candidate for optimization (for example, by adding an index). But in other cases, MySQL might elect not to use an existing index simply because a table is so small that scanning all of its rows is just as fast as using an index.