Getting your MySQL queries right is so important to the performance of your web application. Likewise, getting the indexes on the right columns in the right tables can make the difference between queries that take fractions of a second and queries that can take upwards of 10 seconds.

So you’ve written your application. It works! Happy days. But it’s working like a dog and you suspect MySQL, what do you do next?

Enable MySQL Slow Query log

To enable the mysql slow query log you edit your my.cnf file. In our VPS servers this is almost always in /etc/my.cnf Then add the following under [mysqld] section:

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

Then create the MySQL log directory and slow log file:

mkdir /var/log/mysql
touch /var/log/mysql/log-slow-queries.log
chown mysql.mysql -R /var/log/mysql

When you’ve made the changes to my.cnf and created the log file you need to restart mysql with:

service mysql restart

Just to break down what the changes in my.cnf do:

long_query_time=1 – This tells MySQL to log any query that takes longer that 1 second.
log-slow-queries=/var/log/mysql/log-slow-queries.log – This tells MySQL where to log the slow queries too
log-queries-not-using-indexes – (Optional) Logs queries that don’t use an index, regardless of how long they take

You can test your log file is working by typing

tail /var/log/mysql/log-slow-queries.log

This should out the last few lines logged to file, assuming it’s logged something.

In my next article I will show you how you can analyse what your log has captured to see where you can make improvements.

Thanks for stopping by.