4

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.

8
  • it choose expected index, but only 1st part of it, so need check - why it not take sql part. Not all type combinations work in multi column indexes, it accept combination, but never use it
    – a_vlad
    Apr 5, 2018 at 22:47
  • What's the datatype of sql?
    – user98890
    Apr 5, 2018 at 23:09
  • @Terminus, I've added the column definitions to the question. sql is a varchar. Apr 6, 2018 at 14:23
  • Curious about your query, you will get a set of elapsec_seconds in return. Is it irrelevant which sql had which elapsed_second? Apr 28, 2018 at 13:14
  • @Lennart It is grouped by SQL, so you're getting the maximum elapsed_seconds of each different sql string. Apr 29, 2018 at 19:58

4 Answers 4

2

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.

4
  • 1
    +1 for the analysis. Also, the performance will be affected by the distribution of values (and the ability to "leapfrog"). if only (as an example) only 1000 rows of the (millions rows of the) table pass the created_at >= ... condition, index 1 may perform better than 3. If 100K rows pass but the discreet values of sql are very few, then index 3 may be a better option (with leapfrog). Apr 28, 2018 at 11:22
  • @Rick James Thanks. The MySQL version is "5.6.10 MySQL Community Server (GPL)" Are you suggesting that erasing the other indexes (other than #3) might speed it up? Apr 29, 2018 at 20:50
  • Removing an index sometimes (but rarely) tricks the Optimizer into finding a better way.
    – Rick James
    Apr 30, 2018 at 1:06
  • @RickJames, the MySQL optimizer behaves very weird from time to time. See for example: dba.stackexchange.com/questions/178313/… Apr 30, 2018 at 3:47
1

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.

7
  • The "article" link is is 13 years old. It is probably valid, but does not include recent Optimizer improvements.
    – Rick James
    Apr 28, 2018 at 2:03
  • here and here point to the same Question.
    – Rick James
    Apr 28, 2018 at 2:04
  • @RickJames - thanks for the heads up - corrected!
    – Vérace
    Apr 28, 2018 at 12:01
  • @Vérace Thanks for the idea. I tried hard-coding the date (see updated question) but the speed-up slight :-( Apr 29, 2018 at 20:44
  • Have you tried < NOW() AND > (NOW() - INTERVAL 7 DAY)? With an index on created_at...
    – Vérace
    Apr 29, 2018 at 20:48
1

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.

3
  • Reasonable idea but unfortunately it didn't help much to hard-code the date. See the updated question for details. Apr 29, 2018 at 20:45
  • Can you show the EXPLAIN for modified query?
    – Kondybas
    Apr 29, 2018 at 22:02
  • Added explain to my original index. Let me know if you have any ideas -- I'm very much stumped. May 2, 2018 at 22:19
0

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.

5
  • Column used for grouping not always should be the first in the index definition. Columns used for index should be arranged by cardinality (selectivity) in descendant order. Column that contain unique values should be first while colunm filled with trues/falses should be last.
    – Kondybas
    Apr 28, 2018 at 15:07
  • I would think that a purpose built index could allow an exception (i.e. optimizing the index for a particular query over the best generically useful index). But I'm self taught and recognize there exist significant gaps in my knowledge, so I'll defer to your expertise here.
    – cpcodes
    Apr 30, 2018 at 16:07
  • Indexes are always query-related and can't be derived from the logical structure of the database. Some indexes are created by default - for PK/FK, but all the rest should be created "ad hoc" on demand. Unfortunately there is no such thing "generically useful index". Your database can perform perfectly for the given set of queries but some new query could require new index or number of indexes.
    – Kondybas
    Apr 30, 2018 at 18:54
  • I added an "explain" to my question to show what MySQL is doing with the simplified query. It is using the index you suggested, but is still quite slow. Strange because it's only returning 550 rows in the end. May 2, 2018 at 22:18
  • This may be the extent of the optimization that can be done - you may need to look at hardware or system settings to further speed up the query (allocate more memory or provide faster storage). At 30 million rows, this is not a small feat. For instance, how fast does it return a simple query of the table simply filtered by date? (select sql, created_at, elapsed_seconds from sql_queries where created_at >= '2018-4-22')?
    – cpcodes
    May 2, 2018 at 22:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.