Optimizing slow queries in Mysql
Recently we had to optimize a set of slow queries in our website and I took advantage of this to write a brief article with some optimization tips.
When system performance is slow, before spending a lot of money in hardware, it's a good idea to review your mysql queries, and the best tool to use is called 'EXPLAIN'.
Let's see a example:
`Id` bigint(20) NOT NULL,
`FirstName` varchar(100) NOT NULL,
`LastName` varchar(100) NOT NULL,
`DateOfBirth` date NOT NULL,
`TelephoneNumber` varchar(50) NOT NULL,
`PostalCode` varchar(15) NOT NULL,
`Region` smallint(5) unsigned DEFAULT NULL,
`Municipality` varchar(100) NOT NULL,
`AddressLine` varchar(200) DEFAULT NULL
)
If we want to find the birthday of Peter Pan (Peter Pan is the candidate number 1234), you run: SELECT DateOfBirth FROM Candidate WHERE Id = '1234';
When we run this query, MySQL has to look through all the candidates to find Peter Pan's birthday.
We can avoid this by using an index. An index is a file to help sort a specific field. So, we can create a index on Id field to quickly find any candidate by Id.
But before we create the index let's see how to detect the conditions under which we should create one, and to check this we type the command EXPLAIN before the query:
+----------+------+---------------+------+---------+------+------+-------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +----------+------+---------------+------+---------+------+------+-------------+ | Candidate| ALL | NULL | NULL | NULL | NULL | 1632 | Using where | +----------+------+---------------+------+---------+------+------+-------------+
What does that mean?
- table: Indicates which table the output is affected.
- type: Shows us which type of join is being used. From best to worst the types are: system, const, eq_ref, ref, range, index, all
- possible_keys: Indicates which indices MySQL can choose from to find the rows in this table
- key: Indicates the key (index) that MySQL actually decided to use. If MySQL decides to use one of the possible_keys indexes to look up rows, that index is listed as the key value.
- key_len: It's the length of the key used. The shorter the better.
- ref: Which column (or constant) is used
- rows: The number of rows MySQL believes it must examine to execute the query.
- extra Extra info: the bad ones to see here are "using temporary" and "using filesort"
It seems that our query is not very optimized... There are no possible keys to use, so MySQL has to go through all the 1632 records to find our Peter Pan birthday...
To solve this problem let's create the index we talked above and afterwards we run EXPLAIN again:
+----------+-------+---------------+---------+---------+-------+------+-------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +----------+-------+---------------+---------+---------+-------+------+-------+ | Candidate| const | PRIMARY | PRIMARY | 10 | const | 1 | | +----------+-------+---------------+---------+---------+-------+------+-------+
This query is better than the previous. Type "const" means that the table has only one matching row, and the primary key is being used to find this specific record which reduces the number of rows that MySQL needs to examine to 1.
Be careful using indexes!
It is very important how to use indexes in a correct way, let's see an example:
We want to find all the candidates that are more than 35 years old.
We add an index on DateOfBirth because this column appears in the where clause.
ALTER TABLE Candidate ADD INDEX(DateOfBirth);
Now let's run the query.
+-----------+--------+---------------+------+---------+------+------+------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-----------+--------+---------------+------+---------+------+------+------------+ | Candidate | system | NULL | NULL | NULL | NULL | 10 | where used | +-----------+--------+---------------+------+---------+------+------+------------+
So you can see that all records are being read. It is because of the calculation (DateOfBirth+35). Every DateOfBirth record has to be read in order to add 35 and the index is not being used.
We can resolve this by changing the order of the "where" parameters; instead of "DateOfBirth+35 > now()" we will use "DateOfBirth > now()-35".
Now let's see what happens with this change:
explain SELECT FirstName FROM Candidate WHERE DateOfBirth > now()-35;
+-----------+--------+---------------+-------------+---------+------+------+------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-----------+--------+---------------+-------------+---------+------+------+------------+ | Candidate | system | DateOfBirth | DateOfBirth | 5 | NULL | 1 | where used | +-----------+--------+---------------+-------------+---------+------+------+------------+
Now it's running correctly.. the calculation is done only once, and the index is being used.
About Luis Sobrecueva
No description. Please complete your profile.