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

[Tom Muldoon]

Hi Rick,

After changing the trigger_time column to not null (per your suggestion), I'm afraid that the explain plan remains unchanged (and the expected index is still not used).

Here's the table as it currently exists...

CREATE TABLE `activity_event` (
  `id` bigint(20) NOT NULL,
  `trigger_time` datetime NOT 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__status__trigger_time__id` (`recipient_id`,`status`,`trigger_time`,`id`),
  KEY `recipient_id__trigger_time__id` (`recipient_id`,`trigger_time`,`id`),
  CONSTRAINT `FK84AA016A89E781B9` FOREIGN KEY (`submission_id`) REFERENCES `submission` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

And here's the query and the associated explain plan...

explain    select
        activityev0_.id as id158_,
        activityev0_.trigger_time as trigger2_158_,
        activityev0_.note as note158_,
        activityev0_.type as type158_,
        activityev0_.status as status158_,
        activityev0_.notification_status as notifica6_158_,
        activityev0_.submission_id as submission7_158_,
        activityev0_.actor_id as actor8_158_,
        activityev0_.actor_display_name as actor9_158_,
        activityev0_.actor_user_type as actor10_158_,
        activityev0_.recipient_id as recipient11_158_,
        activityev0_.recipient_display_name as recipient12_158_,
        activityev0_.recipient_user_type as recipient13_158_,
        activityev0_.system_id as system14_158_,
        activityev0_.host_name as host15_158_ 
    from
        activity_event activityev0_
    where
        activityev0_.recipient_id=1
    order by
        activityev0_.trigger_time desc,
        activityev0_.id desc

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

Tom