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

  • 2
    200 ms is very fast and it uses already seat_idx as index – nbk Jun 28 at 13:01
  • 2
    Remove excess assignement, use simple sum(status = 1) as online_count and sum(robot_flag = 1 and status = 1) as robot_online_count. Additionally, if some column may contain 0 or 1 only (no another values at all) then remove excess = 1 check for this column. – Akina Jun 28 at 18:46
  • Let's see SHOW CREATE TABLE; shrinking datasizes where appropriate may help a little. (Eg, TINYINT or SMALLINT instead of INT.) – Rick James Jun 29 at 15:06
  • Please provide EXPLAIN FORMAT=JSON SELECT ...; it may include some useful info. – Rick James Jun 29 at 15:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.