MySQL is a powerful tool but it’s also a great way to shoot yourself in the foot. The other day we received reports of people finding a specific page slow but it was only slow when multiple people where using it.

Using New Relic I was able to determine that on the page in question had a query like the one below which took between 1 and 10 seconds to run. As the number of requests to the page increased so did the amount of time to run the query.

SELECT a.*, r.count
FROM  a
JOIN (
    SELECT COUNT(*) as count, templateId
    FROM b
    WHERE textColumn LIKE "%test%"
    GROUP BY templateId
) as r ON a.templateId = r.templateId              
WHERE a.FKid=12 AND r.count > 0;

When we do an explain on this query we get:

+----+-------------+------------+------------+------+---------------+-------------+---------+----------------------+---------+----------+----------------------------------------------+
| id | select_type | table      | partitions | type | possible_keys | key         | key_len | ref                  | rows    | filtered | Extra                                        |
+----+-------------+------------+------------+------+---------------+-------------+---------+----------------------+---------+----------+----------------------------------------------+
|  1 | PRIMARY     | a          | NULL       | ALL  | NULL          | NULL        | NULL    | NULL                 |       1 |   100.00 | Using where                                  |
|  1 | PRIMARY     | <derived2> | NULL       | ref  | <auto_key1>   | <auto_key1> | 4       | example.a.templateId |    3874 |    33.33 | Using where                                  |
|  2 | DERIVED     | b          | NULL       | ALL  | NULL          | NULL        | NULL    | NULL                 | 2092640 |    11.11 | Using where; Using temporary; Using filesort |
+----+-------------+------------+------------+------+---------------+-------------+---------+----------------------+---------+----------+----------------------------------------------+

What we’re looking at here means MySQL is creating a derived table (line 3) and is looking at all 2092640 rows (because no index is able to restrict it) and then those results are returned and filtered based on the templateId (row 2). It’s also doing a filesort because the results are so large which is a huge performance hit.

To fix this problem the query had to be changed so it uses a subquery:

SELECT a.*, (
    SELECT COUNT(*)
    FROM b
    WHERE textColumn LIKE "%test%"
    and a.templateId = b.templateId
    GROUP BY templateId
) as count
FROM  a
WHERE a.FKid=12 
having count > 0;

And our explain:

+----+--------------------+-------+------------+------+---------------+------------+---------+----------------------+--------+----------+------------------------------------+
| id | select_type        | table | partitions | type | possible_keys | key        | key_len | ref                  | rows   | filtered | Extra                              |
+----+--------------------+-------+------------+------+---------------+------------+---------+----------------------+--------+----------+------------------------------------+
|  1 | PRIMARY            | a     | NULL       | ALL  | NULL          | NULL       | NULL    | NULL                 |      1 |   100.00 | Using where                        |
|  2 | DEPENDENT SUBQUERY | b     | NULL       | ref  | templateId    | templateId | 4       | example.a.templateId | 523160 |    11.11 | Using index condition; Using where |
+----+--------------------+-------+------------+------+---------------+------------+---------+----------------------+--------+----------+------------------------------------+

The reason we were seeing decreasing response time with more users is because each page load cause the system to run this query which took several seconds. During this time another page load would occur which would have to wait for the original to finish and then a third page load would occur again slowing down the process.