How to speed up my mysql query with group by?-CodePudding

Hi all I have this table schema

create table user_activities
(
    id                   int unsigned auto_increment
        primary key,
    user_id              int unsigned                        not null,
    other_user_id        int unsigned                        not null,
    activity_type_id tinyint unsigned                    not null,
    reason               varchar(255)                        null,
    created_at           timestamp default CURRENT_TIMESTAMP not null,
    updated_at           timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP,
    constraint user_activities_user_id_other_user_id_unique
        unique (user_id, other_user_id),
    constraint user_activities_other_user_id_foreign
        foreign key (other_user_id) references users (id),
    constraint user_activities_activity_type_id_foreign
        foreign key (activity_type_id) references user_activity_types (id),
    constraint user_activities_user_id_foreign
        foreign key (user_id) references users (id)
)
    engine = InnoDB
    collate = utf8_unicode_ci;

create index user_activities_other_user_id_index
    on user_activities (other_user_id);

create index user_activities_activity_type_id_index
    on user_activities (activity_type_id);

create index user_activities_user_id_index
    on user_activities (user_id);

The table has now 6515846 rows

Goal

I want to write a query to get the users that had the most recent activity in the last 7 days. I need rows of user_id, mostrecentuseractivitydate

Then in the code I will do some action on them.

My query at the moment is

select updated_at, user_id from  user_activities
where created_at > '2022-08-08 15:16:55'
group by user_id
order by max(updated_at) desc
limit 10;

The explain statement result is

1,SIMPLE,user_activities,,index,"user_activities_user_id_other_user_id_unique,user_activities_user_id_index",user_activities_user_id_index,4,,6416255,33.33,Using where; Using temporary; Using filesort

Problem

The query above with the given schema and number of rows takes forever like 5 minutes... and sometimes I receive no response and query hangs forever

That is not acceptable for my requirement.

Any ideas how to speed that up ?

I have already foreign_key as you can see from table schema on the user_id field and the innodb I think also generates index automatically on the foreign key.

I am also adding the where created_at > clause to reduce only the the items in the 7 days. I even tried without adding the where created_at and did not change much to be honest. Anyway I am interested only in the data from last 7 days so that where clause can stay

CodePudding user response：

You need an index on created_at. Others might be useful as well, but start there.

CodePudding user response：

You want to have a composite key with user_id and created_at in it.

This key should make it possible to do the group by as well as the where clause at the same time.

Try this:

create index user_activities_user_id_created_at on user_activities (user_id, created_at);