MariaDB - Slow GROUP BY on 55 Million row table I have a table with ~55 million rows, which holds user music listening history. I want to generate a report for the last quarter detailing how many times songs have been played per user. The query boils down to:

select userId, trackId, count(*)
from events
-- about 9.379.254 rows in this time range
where occurredAt &gt; '2020-01-01 00:00:00' and occurredAt &lt; '2020-03-01 00:00:00'
group by userId, trackId;


After having above query run for more than two hours I decided to abort the execution. I tried various indexing options, but haven't made any significant progress in terms of performance.

My best attempt made use of the table cluster index. Which had a runtime of 45 minutes:

select userId, trackId, count(*)
from events
where id between
    (select id from events where occurredAt &gt; '2020-01-01 00:00:00' limit 1)
and
    (select id from events where occurredAt &gt; '2020-02-31 00:00:00' limit 1)
group by userId, trackId


Some indexes I've tried:

(occurredAt, userId, trackId)
(userId, trackId)


Using a monthly summary table

I'm planning to introduce a summary table to further improve the performance. Based on the data I believe this will result in a 6x reduction in row size. This of course is a nice optimisation, but I expect more meaningful optimisations can be made elsewhere first.

I'm hoping to find some good suggestions from this community on what can be done to improve the query performance.



The schema:

CREATE TABLE `events` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `userId` varchar(32) NOT NULL,
  `deviceId` varchar(64) DEFAULT NULL,
  `trackId` varchar(32) NOT NULL,
  `playlistId` varchar(32) DEFAULT NULL,
  `bitrate` varchar(32) DEFAULT NULL,
  `duration` int(11) unsigned NOT NULL DEFAULT 0,
  `playTime` int(11) unsigned NOT NULL DEFAULT 0,
  `wasSkipped` tinyint(1) NOT NULL DEFAULT 0,
  `occurredAt` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `events_userid_index` (`userId`),
  KEY `events_trackid_index` (`trackId`),
  KEY `events_playlistid_index` (`playlistId`),
  KEY `events_wasskipped_index` (`wasSkipped`),
  KEY `events_occurredat_index` (`occurredAt`),
  KEY `user_track` (`userId`,`trackId`)
) ENGINE=InnoDB AUTO_INCREMENT=55364153 DEFAULT CHARSET=latin1;
Some bugs:  Each time range leaves out one midnight.  Your first example is 2 months minus one second; nowhere near one "quarter"  The second is one quarter minutes one day minus one second.

As for the attempts you have made in indexing, this one is probably optimal:  (occurredAt, userId, trackId).  Rationale:


The first column completely handles the filtering for the WHERE.
The rest of the columns make the index "covering", so only the index will be used.


Note:  There could be caching issues that confuse the results of timing tests.

The reason the query with an id range may have seemed faster could be caching.  I would not trust it to be faster.

This may work better:  INDEX(userId, tracked, occurredAt)  I say "may" because it depends on a messy optimization that your version may not include.  Note that it could use the first two columns to handle the GROUP BY, thereby avoiding a sort.  And it is "covering".  But it depends on being able to hop through the index.  Suggest getting EXPLAIN FORMAT=JSON SELECT ..., which might provide enough clues.  Or Optimizer Trace.

A big Yes to a summary table.  Depending on the distribution of the data, I might add rows to it each night, then tally up the rows to get the quarterly total.  (This would let you get monthly or weekly tallies, too.  And they would be equally fast.)

A Rule of Thumb when designing a Summary Table:  Aim for one-tenth as many rows in the summary table as in the 'Fact' table.  (If the 'averge' user listens to only one track per day, daily subtotal would be useless.)

More on summary tables:  http://mysql.rjweb.org/doc.php/summarytables

Another issue...  What is the setting of innodb_buffer_pool_size; how much RAM do you have; and how big is the table?  (I would guess 5GB for "Data_length".)  If you have a small machine, we need to discuss shrinking the datatypes, normalization, and tuning.