h2 version? is it possible java version affects optimizer work? looks strange, i have no any message about index used at all.
but even in your case index is used for sorting, isn't it? what about index on join what is most important? where condition is removed , execution time is not decreased. possible to hint query with index? On Friday, July 5, 2013 11:46:45 AM UTC+2, Noel Grandin wrote: > > Works for me: > > create table d1( id bigint, name varchar(50), archived > smallint); > create table d2(id bigint, category varchar(60), source > varchar(100), translation varchar(500)); > create index idx_d1_id on d1(id); > create index idx_d2_id on d2(source); > explain > select d.id, ifnull(translation,name) > from d1 d left join d2 on source=name and category='desc' > where archived=0 > order by d.id; > > gives me: > > SELECT > D.ID, > IFNULL(TRANSLATION, NAME) > FROM PUBLIC.D1 D > /* PUBLIC.IDX_D1_ID */ > /* WHERE ARCHIVED = 0 > */ > LEFT OUTER JOIN PUBLIC.D2 > /* PUBLIC.IDX_D2_ID: SOURCE = NAME */ > ON (CATEGORY = 'desc') > AND (SOURCE = NAME) > WHERE ARCHIVED = 0 > ORDER BY 1 > /* index sorted */ > > On 2013-07-05 11:22, Alex wrote: > > select d1.id ,ifnull(translation,name) from d1 d > > left join d2 on source=name and category='desc' > > where archived=0 order by sort_order,d1.id; > > > > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
