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.