Why is this MySQL query not using the complete index? -
could me query, please?
select p.patid, max(c1.eventdate) eventdate patient p left join op_adv_effects._clinical c1 on p.patid = c1.patid c1.eventdate < p.case_index group p.patid here output of show create table 2 tables:
patient create table `patient` ( `patid` int(10) unsigned not null, `case_index` date not null, 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, `medcode` mediumint(8) 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 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 not using first 2 fields of idx_clin_eventdate_medcode, i.e. (patid,eventdate), patid (see ref column)?
if change condition equality, works fine:
select p.patid, max(c1.eventdate) eventdate patient p left join op_adv_effects._clinical c1 on p.patid = c1.patid c1.eventdate = p.case_index group 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 same results suggested variations:
explain select patid, (select eventdate op_adv_effects._clinical patid = p.patid , eventdate < p.case_index order eventdate desc limit 1 ) eventdate patient p; *************************** 1. row *************************** id: 1 select_type: primary table: p type: index possible_keys: null key: primary key_len: 7 ref: null rows: 182939 extra: using index *************************** 2. row *************************** id: 2 select_type: dependent subquery table: _clinical 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; using filesort explain select patid, ( select max(eventdate) op_adv_effects._clinical patid = p.patid , eventdate < p.case_index) eventdate patient p; *************************** 1. row *************************** id: 1 select_type: primary table: p type: index possible_keys: null key: primary key_len: 7 ref: null rows: 182939 extra: using index *************************** 2. row *************************** id: 2 select_type: dependent subquery table: _clinical 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 the query part of more complex one, reported below. however, 1 example of several complex queries, should use missing part of index on eventdate. reason it's quite important.
create table bmi_lp (primary key (patid)) engine=innodb default charset=latin1 collate=latin1_general_cs select tmp.patid, a2.data3 bmi_lp, tmp.eventdate bmi_lp_date ( select p.patid, max(c.eventdate) eventdate patient p left join op_adv_effects._clinical c1 on p.patid = c1.patid left join op_adv_effects._additional a1 on c1.patid = a1.patid c1.adid <> 0 , c1.adid = a1.adid , a1.enttype = 13 , a1.data3 not null , c1.eventdate < p.case_index group p.patid order p.patid) tmp left join op_adv_effects._clinical c2 on tmp.patid = c2.patid left join op_adv_effects._additional a2 on c2.patid = a2.patid tmp.eventdate = c2.eventdate , c2.adid = a2.adid
due where doing inner join right now. did intend that?
regardless, because of < index cannot used right now, if had index use different order work.
for example, in postgresql this:
create index idx_clin_eventdate_medcode on _clinical (patid asc, eventdate desc); in mysql desc , asc operators no-op unfortunately (with every mysql version 5.7 @ least). unless can reverse query (use > instead of <), mysql can't use index effectively.
note might faster not use index, depends on case. since it's going through 171 rows wouldn't worried.
Comments
Post a Comment