MySQL 5.1 does not seem to be using the index that I expect it to.

[Tom Muldoon]

My table...

CREATE TABLE `activity_event` (
`id` bigint(20) NOT NULL,
`actor_display_name` varchar(255) DEFAULT NULL,
`actor_id` bigint(20) DEFAULT NULL,
`actor_user_type` varchar(255) DEFAULT NULL,
`host_name` varchar(255) DEFAULT NULL,
`note` varchar(16383) DEFAULT NULL,
`notification_status` int(11) DEFAULT NULL,
`recipient_display_name` varchar(255) DEFAULT NULL,
`recipient_id` bigint(20) DEFAULT NULL,
`recipient_user_type` varchar(255) DEFAULT NULL,
`status` int(11) DEFAULT NULL,
`submission_id` bigint(20) NOT NULL,
`system_id` varchar(255) DEFAULT NULL,
`trigger_time` datetime DEFAULT NULL,
`type` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `FK84AA016A89E781B9` (`submission_id`),
KEY `recipient_id__trigger_time__id` (`recipient_id`,`trigger_time`,`id`),
KEY `recipient_id__status__trigger_time__id` (`recipient_id`,`status`,`trigger_time`,`id`),
CONSTRAINT `FK84AA016A89E781B9` FOREIGN KEY (`submission_id`) REFERENCES `submission` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

My query...

explain select
id,
trigger_time,
note,
type,
status,
notification_status,
submission_id,
actor_id,
actor_display_name,
actor_user_type,
recipient_id,
recipient_display_name,
recipient_user_type,
system_id,
from activity_event
where recipient_id=1 and status=1
order by trigger_time desc, id desc;

The explain plan...

1, SIMPLE, activityev0_, ref, recipient_id__trigger_time__id,recipient_id__status__trigger_time__id, recipient_id__trigger_time__id, 9, const, 307, Using where

With that said, MySQL uses the recipient_id__trigger_time__id index but shouldn't it use the recipient_id__status__trigger_time__id index instead?

Note, status has one of two values (0 or 1).