performance - FASTest > mysql get last distinct records return all columns -

performance - FASTest > mysql get last distinct records return all columns -

idx info market po side odd unique_odd 10 927606 ou_ot 2.5 under 2.01 927606_ou_2.5_under 11 927606 ou_ot 2.5 under 2.02 927606_ou_2.5_under 12 927606 ou_ot 2.5 on 1.81 927606_ou_2.5_over 13 927776 ou_ot 3.5 under 1.67 927776_ou_3.5_under 14 927776 ou_ot 3.5 on 2.11 927776_ou_3.5_over 15 927776 ou_ot 3.5 on 2.31 927776_ou_3.5_over

odds_etc database here.

i want output these.

11 927606 ou_ot 2.5 under 2.02 927606_ou_2.5_under 12 927606 ou_ot 2.5 on 1.81 927606_ou_2.5_over 13 927776 ou_ot 3.5 under 1.67 927776_ou_3.5_under 15 927776 ou_ot 3.5 on 2.31 927776_ou_3.5_over

this means distinct unique_odd , max(idx) below sql, takes 7sec. long, want 1~2sec.

select t1.* odds_etc t1 inner bring together (select unique_odd,max(idx) maxidx odds_etc grouping unique_odd) groupt2 on t1.unique_odd = groupt2.unique_odd , t1.idx = groupt2.maxidx info='$info'

with index on odds_etc(unique_odd, idx), next query might have improve performance:

select oe.* odds_etc oe info = '$info' , not exists (select 1 odds_etc oe2 oe2.unique_odd = oe.unique_odd , oe2.idx > oe.idx );

this phrases query as: "get me rows unique_odd has no greater value of idx." more complicated way of saying: "get me row maximum idx each unique_odd." version can take advantage of above index, should improve performance.

you want index on odds_etc(info, unique_odd, idx) well.

edit:

for performance, need next 2 indexes:

create index idx_odds_etc_unique_odd_idx on odds_etc(unique_odd, idx); create index idx_odds_etc_info_unique_odd_idx on odds_etc(info, unique_odd, idx);

mysql performance max distinct