Slow MySQL Queries - Finding and Fixing

Edit /etc/mysql/my.cnf . Uncomment the following lines:

log_slow_queries        = /var/log/mysql/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes

This enables logging of slow queries and queries not making use of indexes.

Now tail -f the mysql-slow.log file. You’ll see the slow and non-index-using queries.

Grab a query that you’d like to examine. Open a mysql shell and run “explain” on it:

explain your_query_here;

You’ll see output that looks like:

+----+-------------+---------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table               | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | some_table          | ALL  | NULL          | NULL | NULL    | NULL |  166 | Using where |
+----+-------------+---------------------+------+---------------+------+---------+------+------+-------------+

Look at the rows and key fields in particular. rows shows how many rows were examined for query – you don’t want to see a high number. If you’re not using an index you may be doing a full-table-scan, examining every single row to find the value you’re looking for.

key shows which index, if any, was used. If you see NULL then no index was used.

To make the query go faster you may need an index. Look at the query and see what your’e selecting based on, and then create the corresponding index. For example, if we’re doing the selection based on the “myfield” field, you could

create the index:

CREATE INDEX myapp_mytable_myfield_idx on myapp_mytable(myfield);

Once you create the index you should see that query no longer appearing in the slow query log.
Rinse and repeat for other queries.