How to Select the Best Query Candidates for Optimization

If your application executes several different queries, changes are, that one or two of the queries might be slowing the total query log, because they are not properly optimized. But there is a way, how you can by yourself find out, which queries need optimization.

First you need to turn the query log on, or if you want to just see the slow queries, turn on the slow log. The MySQL log files are explained at http://www.mysql.com/doc/en/Log_Files.html (http://www.mysql.com/doc/en/Log_Files.html)

When you've run your application, take a look at the log and try to spot distinct query types. By distinct types we mean queries which are different more than just in some constants.
I.e.
select * from a where id=5
and
select * from b where id=7
fall in the same type and you'll need to analyze only one of them.

The next step is to select the queries which are the best candidates for optimization. This is usually done by the following criterias:
* Query is very often executed in your application
* Query scans large amount of rows, especially if this results just in a few rows sent. Numbers more than 1000-10000 shall be treated as large based on your application
* Queries which just are the slowest - taking largest amount of time to execute.

As soon as you've spotted the best candidates for optimization run EXPLAIN SELECT .... to get info how it is optimized.

If you have UPDATE/DELETE query EXPLAIN will not work, but you can rewrite it to SELECT query to make it work.

E.g.
update a set t=5 where b=7 and d=8
can be rewritten for EXPLAIN to work as:
select * from a where b=7 and d=8

You can read in more detail about EXPLAIN at
http://www.mysql.com/doc/en/EXPLAIN.html (http://www.mysql.com/doc/en/EXPLAIN.html)