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:
- 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