I have been trying to optimize a slow query with indexes, but it won’t work and i can’t figure out why.
This is the slow query:
Query_time: 14 Lock_time: 0 Rows_sent: 1 Rows_examined: 15923
SELECT COUNT(id) FROM messages WHERE recipientid=123444 AND status=1;
I have a multiple column index for recipientid and status (in that order) to satisfy the query WHERE syntax. Despite this, the query still takes 14 seconds (
messages.recipientid is a MEDIUMINT (7) UNSIGNED NOT NULL
messages.status is a TINYINT (1) UNSIGNED NOT NULL
This is the EXPLAIN results:
id select_type table type possible_keys key key_len ref rows Extra1 SIMPLE messages ref recipientid recipientid 4 const,const 8352
It seems as though MySQL is not traversing the multiple column index to the status column. I have tried FORCE INDEX (recipientid) but this doesn’t help.