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