MySQL Forums
Forum List  »  Optimizer & Parser

Re: MySQL 5.1 does not seem to be using the index that I expect it to.
Posted by: Tom Muldoon
Date: April 05, 2011 05:07AM

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`)

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

explain    select 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_ 
        activity_event activityev0_
    order by
        activityev0_.trigger_time desc, 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'


Options: ReplyQuote

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.