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 
  • Did you consider adding an index for columns user_message_id, msg_status?
    – SaschaM78
    Mar 25, 2022 at 10:52

1 Answer 1


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

  • Thanks @RolandoMySQLDBA . It worked ! Mar 25, 2022 at 11:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.