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 , room_id, app_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?