samedi 25 avril 2015

Why is this MySQL query not using the complete index


Could you help me with this query, please?

SELECT p.patid, MAX(c1.eventdate) as eventdate 
from patient as p 
left join op_adv_effects._clinical as c1 on p.patid = c1.patid 
where c1.eventdate < p.case_index 
group by p.patid

Here is the output of SHOW CREATE TABLE for the 2 tables:

patient CREATE TABLE `patient` (
  `patid` int(10) unsigned NOT NULL,
  `case_index` date NOT NULL,
  `pracid` smallint(5) unsigned DEFAULT NULL,
  `first_registration_date` date DEFAULT NULL,
  `transfer_out_date` date DEFAULT NULL,
  `transfer_out_reason` varchar(100) COLLATE latin1_general_cs DEFAULT NULL,
  `last_collection_date` date DEFAULT NULL,
  `deathdate` date DEFAULT NULL,
  `birth_year` int(6) unsigned DEFAULT NULL,
  `gender` varchar(100) COLLATE latin1_general_cs,
  `marital_in_gprd` varchar(100) COLLATE latin1_general_cs,
  `strategic_health_authority` varchar(100) COLLATE latin1_general_cs,
  PRIMARY KEY (`patid`,`case_index`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs

_clinical   CREATE TABLE `_clinical` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `patid` int(10) unsigned NOT NULL,
  `eventdate` date NOT NULL,
  `sysdate` date DEFAULT NULL,
  `constype` tinyint(3) unsigned DEFAULT NULL,
  `consid` int(10) unsigned NOT NULL,
  `medcode` mediumint(8) unsigned DEFAULT NULL,
  `staffid` int(10) unsigned DEFAULT NULL,
  `textid` varchar(50) COLLATE latin1_general_cs DEFAULT NULL,
  `episode` tinyint(3) unsigned DEFAULT NULL,
  `enttype` tinyint(3) unsigned DEFAULT NULL,
  `adid` mediumint(8) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_clin_eventdate_medcode` (`patid`,`eventdate`,`medcode`),
  KEY `idx_clin_eventdate_adid` (`patid`,`eventdate`,`adid`)
) ENGINE=InnoDB AUTO_INCREMENT=62407536 DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs

"explain" returns the following:

*************************** 1. row ********************
           id: 1
  select_type: SIMPLE
        table: p
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 7
          ref: NULL
         rows: 182939
        Extra: Using index
*************************** 2. row ********************
           id: 1
  select_type: SIMPLE
        table: c1
         type: ref
possible_keys: idx_clin_eventdate_medcode,idx_clin_eventdate_adid
          key: idx_clin_eventdate_medcode
      key_len: 4
          ref: gprd_opadveff_extra_elisa.p.patid
         rows: 171
        Extra: Using where; Using index

Why is it not using the first 2 fields of idx_clin_eventdate_medcode, i.e. (patid,eventdate), but only patid (see ref column)?

If I change the where condition to equality, it works fine:

SELECT p.patid, MAX(c1.eventdate) as eventdate 
from patient as p 
left join op_adv_effects._clinical as c1 on p.patid = c1.patid 
where c1.eventdate = p.case_index 
group by p.patid

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: p
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 7
          ref: NULL
         rows: 182939
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: c1
         type: ref
possible_keys: idx_clin_eventdate_medcode,idx_clin_eventdate_adid
          key: idx_clin_eventdate_medcode
      key_len: 7
          ref: gprd_opadveff_extra_elisa.p.patid,gprd_opadveff_extra_elisa.p.cas
e_index
         rows: 1
        Extra: Using index

Thank you a lot in advance


Aucun commentaire:

Enregistrer un commentaire