Optimizing MySQL Queries

When you first build a website the database may seem fast. All your queries may get executed quickly. But, after a while when the database is much larger the queries may start taking longer. If you haven’t already, it may be time to optimize the MySQL queries!

This is a quick guide to optimizing MySQL queries. It’s more general and theoretical than being a step-by-step tutorial. Much of this can be done in a graphical interface but here I’m using MySQL in the command line.

In the Beginning

Creating your tables properly in the first place will save you headaches down the road. In the beginning, when you are creating your tables make sure that all the columns are of the right type (INT, VARCHAR, TEXT, ENUM) and that they have a size where possible. For some columns, it’s better to use varchar than text because varchar is limited to 255 characters while text is pretty much unlimited. However, you’ll probably need some text columns too so it’s just a case of using them in a way that should be relatively painless as the site grows.

Explain

Put EXPLAIN before your MySQL query to find out what is going on. It will give you various useful information such as the type of query MySQL is running on each table in the query and the number of rows it is searching through.

The possible types are (good to bad)…

1. const/eq_ref
2. ref/range
3. index
4. all

It’s better to have the type “index” than the type “all”, but “eq_ref” is better still. Having a type of “all” is the worst thing for your query.

Select Explicitly

Select explicitly, don’t use SELECT *. The worst thing to do is return everything when you have a large number of columns in the table and you only need one or two columns. Only selecting what you need will make a more manageable MySQL query.

If you are doing a large search and returning a lot of rows, is there a better or quicker way to do what you are trying to do. One possibility might be to only return the id of the rows then if you need more information you can get it later. That’s just a possibility and would depend on the query and what you needed to do.

Remove Functions

In some RDS databases you are able to use functions and still be optimized, not in MySQL. Using MySQL functions in the query means that an index won’t work.

This is an example of using the year() function. This is bad…

SELECT id FROM blog WHERE YEAR(date)='2016' ORDER BY id DESC

It’s better to use BETWEEN for dates…

SELECT id FROM blog WHERE date BETWEEN 2016-01-01 AND 2017-01-01 ORDER BY id DESC

or

SELECT id FROM blog WHERE date >= 2016-01-01 AND date < 2017-01-01 ORDER BY id DESC

Indexing

So, you’re selecting only what you want from the query and not using any functions, it’s probably time to index!

The aim is to not have any “all” types for any of the tables when you run the EXPLAIN on your query. You might also be able to get the number of rows searched down, but that might not be possible.

Simply, you put everything from a table in a query into the index.

The order of the indexed columns matters!

Run the query you are trying to optimize in MySQL or PHPmyAdmin making a note of how long the query takes to execute. Add the indexes, run an explain, then tweak the indexes, or if they look ok, run the actual query. When you run the query after optimizing it should be quicker, or at least not worse. If the query is worse your index may have columns in the wrong order.

One thing to remember is that making an index for one query might speed up that query but if another query uses the same index it may actually slow that other query down.

Also, indexing properly should speed up SELECT queries but it will have the opposite effect on UPDATE and INSERT statements.

Limit

LIMIT doesn’t necessarily mean you only search that number of records. You may still be searching all the records then discarding the rest. Or, you may only be searching that number of records when you need to search the entire database. Use LIMIT with care!