Fast MySQL query gets very slow when adding SUM-CodePudding

I have this query, running on AWS Aurora, in MySQL mode, and it is very fast (100ms):

select rv.kind, rv.sub_kind, count(*)
from revenues rv
where rv.paid_at = '2021-11-17'
group by rv.kind, rv.sub_kind;

However, if I add a sum, it gets VERY slow (1 minute and 20 seconds):

select rv.kind, rv.sub_kind, count(*), sum(iugu_fee_cents)
from revenues rv
where rv.paid_at = '2021-11-17'
group by rv.kind, rv.sub_kind;

Its a simple query, and I have a composite index which includes paid_at, kind, and sub_kind, in this order. Using EXPLAIN on both queries confirm the index is being used.

The other info is the same for both queries, except the Extra column, which shows Using where; Using index for the fast query, and Using index condition in the slow query.

Is there anything I can do to solve this issue?

CodePudding user response：

The index you already have (paid_at, kind, sub_kind) is very good at finding and selecting rows.

This index includes all the information the first query needs to return the results, but not all the information the second query needs. Therefore, the second query requires to perform a "secondary index seek" on the table. That is common in index-based engines.

In general, that's not a big problem, unless you need very high performance, or if you are selecting a massive number of rows.

If you need the query to be faster you can try replacing that index with a covering index. The covering index won't suffer from the "secondary index seek" problem and should be faster.

In this case you should create the index:

create index ix1 on revenues (paid_at, kind, sub_kind, iugu_fee_cents);