Re: Slow COUNT + GROUP BY

[Øystein Grøvlen]

Hi,

The only explanation I can think of for why a scan using the primary key is quicker than using the index, is that the PRIMARY key is to a larger degree cached in memory than what is the case for the index on day column. Do the queries show the same timing behavior if you repeat them a few times?

I also see that the PRIMARY KEY contains all columns of the table. That means that any secondary index will be as large as the primary key index (i.e., the whole table). Still, it should be cheaper to scan a part of the index instead of the whole table expect when the index range scan requires more I/O. For more efficient secondary indexes you may consider using an artificial primary key (e.g., an AUTO_INCREMENT column). Then the day index will be much smaller.

Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway