Database Development

how to optimize group by query in mysql 5.7

I am using MySQL 5.7 and I have a sql query like this:

select sum(case when `status` = 1 then 1 else 0 end) as online_count,
sum(case when robot_flag = 1 and `status` =1 then 1 else 0 end) as robot_online_count,
room_play_id ,
from r_room_seat
where app_id = 4
group by app_id,room_id,room_play_id

the r_room_seat has 40775 rows, and app_id = 4 have 1780 rows. one app have n room, one room have n room_play, one room_play have n seat, now the sql execuate takes 200ms +, I have tried to create index like this:

create index seat_idx on r_room_seat(app_id,room_id,room_play_id);

still not change, any good ideas?

enter image description here

Leave a Reply

Your email address will not be published. Required fields are marked *