Optimize MySQL query with MAX, GROUP BY, and WHERE

Question

I have a fairly simple query that I've failed to optimize sufficiently despite adding a bunch of indexes.

The query is:

SELECT max(elapsed_seconds)
FROM sql_queries
WHERE created_at >= now() - interval 1 week
GROUP BY `sql`

I've tried adding the following indexes:

  KEY `sql_queries_sql_index` (`sql`),
  KEY `sql_queries_elapsed_seconds_index` (`elapsed_seconds`),
  KEY `sql_queries_created_at_index` (`created_at`),
  KEY `sql_queries_sql_created_at_index` (`sql`,`created_at`),
  KEY `sql_queries_sql_elapsed_seconds_index` (`sql`,`elapsed_seconds`),
  KEY `sql_queries_created_at_sql_elapsed_seconds` (`created_at`,`sql`,`elapsed_seconds`)

Obviously there's too many (and redundant) indexes -- I just kept adding them hoping the query would run faster.

The table has 24 million rows and the query currently takes about four minutes.

"explain" shows:

+----+-------------+-------------+------------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------+---------+------+----------+----------+-----------------------------------------------------------+
| id | select_type | table       | partitions | type  | possible_keys                                                                                                                                                        | key                                        | key_len | ref  | rows     | filtered | Extra                                                     |
+----+-------------+-------------+------------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------+---------+------+----------+----------+-----------------------------------------------------------+
|  1 | SIMPLE      | sql_queries | NULL       | range | sql_queries_sql_index,sql_queries_created_at_index,sql_queries_sql_created_at_index,sql_queries_sql_elapsed_seconds_index,sql_queries_created_at_sql_elapsed_seconds | sql_queries_created_at_sql_elapsed_seconds | 5       | NULL | 11574092 |   100.00 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+-------------+------------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------+---------+------+----------+----------+-----------------------------------------------------------+

The column definitions are:

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sql` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `elapsed_seconds` double DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,

I'd like to do avg and count on each group, but I've omitted that to simplify discussions.

Any ideas / tips are greatly appreciated.

--

Update 1: I tried simplifying the query by hard-coding the date like this:

select max(elapsed_seconds) from sql_queries where created_at >= '2018-4-22' group by `sql`;

The query time (after doing a first query to warm up the caches) decreases from 2 min 5 sec to 1 min 53 secs. So not a significant improvement.

Update 2:

Here's the explain statements for this simplified query:

mysql> explain select max(elapsed_seconds) from sql_queries where created_at >= '2018-4-22' group by `sql`;
+----+-------------+-------------+------------+-------+-----------------------------------------------------------------------------------------------+--------------------------------------------+---------+------+----------+----------+--------------------------+
| id | select_type | table       | partitions | type  | possible_keys                                                                                 | key                                        | key_len | ref  | rows     | filtered | Extra                    |
+----+-------------+-------------+------------+-------+-----------------------------------------------------------------------------------------------+--------------------------------------------+---------+------+----------+----------+--------------------------+
|  1 | SIMPLE      | sql_queries | NULL       | index | sql_queries_sql_index,sql_queries_created_at_index,sql_queries_sql_created_at_elapsed_seconds | sql_queries_sql_created_at_elapsed_seconds | 780     | NULL | 29773986 |    50.00 | Using where; Using index |
+----+-------------+-------------+------------+-------+-----------------------------------------------------------------------------------------------+--------------------------------------------+---------+------+----------+----------+--------------------------+

Update 3:

As a sanity check I tried removing the date constraint and added an index on (sql, elapsed_seconds). The query was then instantaneous.

Answer 1

There are only three serious candidates:

(`created_at`,`sql`,`elapsed_seconds`) -- 1
(`created_at`,`elapsed_seconds`,`sql`) -- 2
(`sql`,`created_at`,`elapsed_seconds`) -- 3

Both are "covering". That is, the query can be handled entirely in the index. EXPLAIN indicates such by saying Using index.

Analysis:

(`created_at`,`sql`,`elapsed_seconds`) -- 1
(`created_at`,`elapsed_seconds`,`sql`) -- 2

filter first. But then the rest of the index is not in any useful order. So it sorts to do the GROUP BY and eventually finds the max. It cannot simply reach for the 'last' entry to get MAX. I don't think either of these is better than the other of the two.

(`sql`,`created_at`,`elapsed_seconds`) -- 3

might avoid the sort, since the sql values come one at a time. Also, the Optimizer might be able to jump to the starting point in the index for the desired created_at (for each sql). Again, it cannot simply reach for the 'last' entry to get MAX.

I vote for #3. However, this is an area where there have been optimization improvements. That is, an older version of MySQL may not do, for example, the leapfrogging.

Answer 2

Your problem I think is that this is not SARG-able

where created_at >= now() - interval 1 week group by `sql`

You already have an index on created_at. Extra INDEXes only affect performance for INSERTs and DELETEs and not SELECTs but that's no excuse for having too many!

Take a look at what MySQL's optimiser has to say about the different queries you may try! A word of warning though - the MySQL optimizer is (ahem...) a notoriously fickle piece of software so YMMV!

This article is a good place to start!

This section is of particular interest in our case:

But suppose that you don't have a specific date. You might be interested instead in finding records that have a date that lies within a certain number of days from today.

We're right on the money here!

There are several ways to express a comparison of this type — not all of which are equally efficient. Here are three possibilities:

WHERE TO_DAYS(date_col) - TO_DAYS(CURDATE()) < cutoff

WHERE TO_DAYS(date_col) < cutoff + TO_DAYS(CURDATE())

WHERE date_col < DATE_ADD(CURDATE(), INTERVAL cutoff DAY)

For the first line, no index is used because the column must be retrieved for each row so that the value of TO_DAYS(date_col) can be computed.

OK, so scrap that one!

The second line is better. Both cutoff and TO_DAYS(CURDATE()) are constants, so the right-hand side of the comparison can be calculated by the optimizer once before processing the query, rather than once per row. But the date_col column still appears in a function call, preventing use of the index.

And that one!

The third line is best of all. Again, the right-hand side of the comparison can be computed once as a constant before executing the query, but now the value is a date. That value can be compared directly to date_col values, which no longer need to be converted to days. In this case, the index can be used.

So, maybe your query will work better with something like (don't have a server that I can test!)

WHERE created_at > DATEADD(NOW() - INTERVAL 7 DAY)

You could also look at the links here.

Answer 3

The main problem I've see is that WHERE clause performs non-constant comparison.

For each of the 11574092 rows the same value now() - interval 1 week calculated again and again that produce the overhead. Moreover, index can't help with such kind of comparison as far as now() is non-deterministic function that can return different results in two consequent invocation. Therefore engine is forced to check all and every row for desired condition, calculating now() - interval 1 week each time from the scratch.

It's easy to avoid this very common trap. Calculate the value once and store it in the user-defined variable:

SET @starting_point = now() - interval 1 week;

SELECT max(elapsed_seconds)
  FROM sql_queries
 WHERE created_at >= @starting_point
 GROUP BY `sql`
;

You already have the best matching index chosen by the optimizer (created_at,sql,elapsed_seconds) now see how it works with constant comparison.

Answer 4

I'm not an expert, but I have been working with SQL for a while, so I think I can help here.

Creating the correct index for this query should look like this:

What are you grouping by? "sql", so this should be the first field of your index.

What will the query be filtered on? "created_at", so this should be the second field

Lastly, you then add all the remaining fields that you will need for the actual calculations. This is optional, but will improve performance as the database engine then won't have to even access the table itself if it can find everything in the index.

So, your ideal index should be (potentially adding more fields after 'elapsed_seconds' if needed):

KEY `sql_queries_sql_created_at_elapsed_seconds` (`sql`,`created_at`,`elapsed_seconds`)

Since this is a MAX() calculation, the query essentially turns into a simple search of the sorted index, so it could theoretically be very fast.