I am trying to do a simple per month of year sum of values on a big (>10M) table. However, it faces significant performance issues (7 sec with 4G innodb pool size, in local db with 1G innodb pool size it takes over a minute). The script seems fairly simple.

FROM measurements
  source_id = 2
GROUP BY year(date_time), month(date_time)

I have tried using MySQL 5.7 and MariaDB 10.2. Using EXPLAIN in both cases provides:

SIMPLE  measurements    ref source_id,source_date   source_id   4   const   4418476 Using where; Using temporary; Using filesort

It seems I cannot avoid using temporary table. The table has 2 indexes: date_time and source_id, date_time.

  • Try an index on (date_time, souce_id, value) -- or (source_id, date_time, value) if the cardinality of source_id is high.
    – mustaccio
    Nov 12, 2017 at 16:00
  • You said you have two indexes / keys: date_time and (source_id, date_time). However your EXPLAIN indicates a key called source_id of key_len 4. If this is the second key, then the length should be 9. Or at least that is what I get.
    – dbdemon
    Nov 12, 2017 at 17:02
  • 1
    @mustaccio - Cardinality does not matter; putting source_id first (because of =) does matter.
    – Rick James
    Nov 12, 2017 at 19:28
  • @mustaccio adding (source_id, date_time, value) index did work splendid! the time is now down to <4 sec. Nov 12, 2017 at 19:50
  • @dbdemon I am not sure I understand how the key length is calculated - what its importance is. I am fairly new to EXPLAIN analytics. Nov 12, 2017 at 19:51

1 Answer 1

  • INDEX(source_id, date_time, value) is optimal, partially because it is "covering". "Covering" means that the query can be completely handled by the columns in the index. I am assuming you did not water down the query?
  • The column tested by = must come first, regardless of cardinality. Else, If date_time is first, it will have to read the entire index.
  • The EXPLAIN estimates that source_id = 2 44% (4418476/10M) of the time. Reading 4M rows is a lot better than 10M.
  • Please provide SHOW CREATE TABLE; without it, I am making guesses in my Answer.
  • Both of your attempted indexes have to bounce between the index BTree and the Data BTree.
  • Probably between 1GB's and 4GB's worth of blocks (16KB - data or index) needed to be pulled into cache (buffer_pool) to satisfy your query. Probably it was entirely in the 4GB buffer_pool when it took 7s. And in the 1GB, there was not room, so it was at least partially I/O-bound.
  • The suggested index will easily fit in 1GB buffer_pool. And only 44% of it is needed for this query.
  • When you add my composite query, get rid of the existing one that is a prefix of it; it will be redundant and unnecessary.
  • As your data grows, someday the index will become too big for 1GB. That's a fact of life.
  • However, by building and maintaining a [Summary table(http://mysql.rjweb.org/doc.php/summarytables), you can make the equivalent query take less than 1 second on either server, even as the table grows. (It would probably have 3 columns: source_id, date (no time), and sum(value).)
  • No, this is the exact query. The rest table columns are not really relevant since they are mostly strings conveying more meaning to the value. However, adding the new index did work (less than 4 sec down from almost 2 minutes in the 1GB). I am not really sure how adding the value helped since it was not part of the GROUP BY or the WHERE clause. Nov 12, 2017 at 19:52
  • 1
    @MikeDrakoulelis - like Rick James said, adding value to the index makes it covering, i.e. that the query can be executed using the index only, i.e. no need to go to the table itself.
    – dbdemon
    Nov 12, 2017 at 22:08

Your Answer

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

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