mysql query takes too long for a million row table using count() and group by



  • I want to retrieve phone_num and their count of unread message for each phone number from the table user_message. Below mentioned query takes 10 seconds to give result which is super duper slow for me.

    Extra Info of table:

    1. I have Indexed Column phone_num and user_message_id
    SELECT phone_num,
           Count(phone_num) AS no_of_messages
    FROM   user_message
    WHERE  user_message_id = "rd62tdw4n3"
           AND msg_status = "unread"
    GROUP  BY phone_num 
    


  • You need a compound index as follows

    ALTER TABLE user_message
    ADD INDEX covering_index (user_message_id,msg_status,phone_num);
    

    The query will read everything from this index and never touch the table




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2