If you're serious about web performance optimization, then you must run an efficient database. One necessary skill is an ability to analyze a database slow query log and optimize the most expensive, slowest results. While one relatively slow query may not be the end of the world, many slow queries will add up quickly and frustrate users.

Setting Up a Slow Query Log

Running a slow query log is not a default setting in MySQL, so the first thing you’ll need to do is set one up. Open the server’s configuration file and enter “log-slow-queries” as well as an output file. On the next line, enter “long_query_time=x”. The value x represents a certain number of seconds, and any query taking longer than that number will appear in the output log.

Although needs will vary, a value of 1 is generally recognized as a good starting point. For heavily used servers, a value of 1 may result in a slow query log which is too large, creating additional performance problems.

In addition, you may want add a third line which reads “log-queries-not-using-indexes”. (Evidently the creators of MySQL did not realize that the plural of "index" is actually "indices.") This will instruct MySQL to log any query which does not use an index, whether or not the query time exceeds the value listed on the second line. If you only want to log queries not using indices, you can set the value on the second line very high.