Adam Jimenez | Tips for optimising MySQL queries

Tips for optimising MySQL queries

Database queries can very often be the bottle-neck that slows down a webpage. If you have a busy server with lots of sites or lots of webpages it's not always apparent which database queries are causing the problems.

Logging slow queries

A good place to start is by logging slow queries. You can do this by adding the following lines to your MySQL config file (usually /etc/my.cnf)

set-variable=long_query_time=1
log_slow_queries = /var/log/mysql/mysql-slow.log

Create the log file and give it write access:

touch /var/log/mysql/mysql-slow.log
chmod 777 /var/log/mysql/mysql-slow.log

You will then need to restart mysql:

service mysqld restart

After a while the log file should start to fill up with a list of queries that have taken longer than 1 second.
Not all of these queries will be poorly optimised - it could be that some queries can't be further optimised or that they took a long time because the server was very busy. Even so it is a good indicator and you can look through the list of queries and start optimising them.

Indexes

Adding indexes can have a dramatic affect on speed. You should look at the table joins and WHERE conditions of the query to see where indexes could be used.

Take these examples.

SELECT * FROM users WHERE name ='Joe'

Adding an index to the "name" column will significantly improve this query.

SELECT * FROM users WHERE name ='Joe' AND surname='Schmoe'

Adding one combined index for "name" and "surname" will help here.

SELECT * FROM users WHERE email='joe.schmoe@gmail.com'

In this case I would use a unique index for the email column. Unique means that the email address can only appear once in the table. This is much faster than a regular index and also enforces the database integrity.

Note that indexes take up space and slow down insert/ updates - but this is generally a small price to pay for much faster select statements.

Correct field types

It's worth looking over the database structure and seeing if a TINYINT could be used instead of an INT. Or an ENUM instead of a VARCHAR. Also check the size of the VARCHAR columns and adjust accordingly. Check if INT fields should be UNSIGNED. An UNSIGNED INT can only be positive (>=0). Unsigned INTs should always be used for ID columns - and again this helps with your database integrity. You may need to run "OPTIMIZE table" before you see any benefit. You probably won't see a drastic improvement - but these little changes all add up.

Other tips

Joins are an expensive operation and should only be used when absolutely necessary. Check the fields you are selecting and your WHERE conditions to see if you really need each join.

Only select what you need to, so if you only need to fetch an ID instead of doing: