Mysql – Optimize MySQL query with MAX, GROUP BY, and WHERE

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.