Hi, H2 currently uses only one index. What you could try is using "union":
select * from TEST where a = '123' and b = '456' and c='PP' union select * from TEST where a2='1234' and b = '456' and c='PP' Ideally, H2 should automatically do that for you. But well, right now, it doesn't Regards, Thomas On Thursday, July 30, 2015, Steve McLeod <[email protected] <javascript:_e(%7B%7D,'cvml','[email protected]');>> wrote: > Indexing for an OR clause that references two separate columns is a > difficult task. > > Does your use case tolerate an index on columns b and c only? > > When I add this index to your example code: > create index IDX_TEST_4 on TEST(b,c); > > I get this query plan: > SELECT > TEST.A, > TEST.A2, > TEST.B, > TEST.C > FROM PUBLIC.TEST > /* PUBLIC.IDX_TEST_4: C = 'PP' > AND B = '456' > */ > WHERE (C = 'PP') > AND ((B = '456') > AND ((A = '123') > OR (A2 = '1234'))); > > On Tuesday, 28 July 2015 23:23:38 UTC+2, epsilon68 wrote: >> >> >> On Tue, Jul 28, 2015 at 8:53 AM, Noel Grandin <[email protected]> wrote: >> >>> >>> Try doing this: >>> >>> select * from TEST where (a = '123' and b = '456' and c='PP') or >>> (a2='1234' and b = '456' and c='PP'); >> >> >> same result >> >> explain select * from TEST where (a = '123' and b = '456' and c='PP') or >> (a2='1234' and b = '456' and c='PP'); >> PLAN >> <http://192.168.5.85:8082/query.do?jsessionid=1bfd56e4866af5b58a02a0da0e930c6e#> >> SELECT >> TEST.A, >> TEST.A2, >> TEST.B, >> TEST.C >> FROM PUBLIC.TEST >> /* PUBLIC.TEST.tableScan */ >> WHERE ((C = 'PP') >> AND ((A = '123') >> AND (B = '456'))) >> OR ((C = 'PP') >> AND ((A2 = '1234') >> AND (B = '456'))) >> >> -- > 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/d/optout. > -- 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/d/optout.
