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

[Tom Muldoon]

Ver 14.14 Distrib 5.1.56, for Win32 (ia32)

After making the suggested changes, I dropped and recreated both indexes and while I'm happy to report that my aforementioned query is now using the expected index, recipient_id__status__trigger_time__id, I'm afraid that a second query (one which does not include the status column) is not using the recipient_id__trigger_time__id index (and is using a filesort).

Here's my table (after the changes)...

'CREATE TABLE `activity_event` (
`id` bigint(20) NOT NULL,
`trigger_time` datetime DEFAULT NULL,
`note` varchar(16383) DEFAULT NULL,
`type` int(11) DEFAULT NULL,
`status` tinyint(4) NOT NULL,
`notification_status` int(11) DEFAULT NULL,
`submission_id` bigint(20) NOT NULL,
`actor_id` bigint(20) DEFAULT NULL,
`actor_display_name` varchar(255) DEFAULT NULL,
`actor_user_type` varchar(255) DEFAULT NULL,
`recipient_id` bigint(20) NOT NULL,
`recipient_display_name` varchar(255) DEFAULT NULL,
`recipient_user_type` varchar(255) DEFAULT NULL,
`system_id` varchar(255) DEFAULT NULL,
`host_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `FK84AA016A89E781B9` (`submission_id`),
KEY `submission_id__trigger_time__id` (`submission_id`,`trigger_time`,`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 aforementioned query (which includes the status column) now uses the expected index. Here's the explain plan...

'1', 'SIMPLE', 'activityev0_', 'ref', 'recipient_id__trigger_time__id,recipient_id__status__trigger_time__id', 'recipient_id__status__trigger_time__id', '9', 'const,const', '46', 'Using where'

My query which does not include the status column, however, does not use recipient_id__trigger_time__id as expected. Here's the explain plan (note the filesort)...

'1', 'SIMPLE', 'activityev0_', 'ref', 'recipient_id__trigger_time__id,recipient_id__status__trigger_time__id', 'recipient_id__status__trigger_time__id', '8', 'const', '333', 'Using where; Using filesort'