Hi, A combined index is sometimes better, see http://h2database.com/html/performance.html#storage_and_indexes
Regards, Thomas On Fri, May 17, 2013 at 10:23 AM, Rami Ojares <[email protected]> wrote: > Try creating indexes separately. > Like this: > create index T1_IN on test1(REC_ID) > create index T2_IN on test1(SIDE_ID) > > - rami > > > On 17.5.2013 3:39, Shiva wrote: > >> >> I am using the following query and noticed a table scan on table "test1" >> instead of using the index. >> I really need to speed up this query since the tables will have 5 to 10 >> million records. >> >> explain select p.upc from test1 t1 >> join test2 t2 on t2.rec_id = t1.rec_id >> join test3 t3 on t1.side_id = t3.side_id >> join test4 t4 on t3.upc = t4.upc >> where t2.source = 'xyz' and t4.sales_id = 123 order by t3.upc_rank limit >> 1; >> >> The table test1 has an index which was created using "create index T1_IN >> on test1(REC_ID, SIDE_ID)", >> the interest thing is if I switch the order of index to >> test1(SIDE_ID,REC_ID) then it uses the index. >> >> All the tables listed in the query has multiple indexes defined for all >> of the ids used. >> >> Is there a way to force this query to use specific index for each table ? >> >> I read somewhere we can break the query to use union so that it uses the >> index, I am not a query expert and having difficulty in breaking them. >> >> I appreciate any help in this regard. >> >> Thanks >> Shiva >> -- >> 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 >> h2-database+unsubscribe@**googlegroups.com<h2-database%[email protected]> >> . >> To post to this group, send email to [email protected]. >> Visit this group at >> http://groups.google.com/**group/h2-database?hl=en<http://groups.google.com/group/h2-database?hl=en> >> . >> For more options, visit >> https://groups.google.com/**groups/opt_out<https://groups.google.com/groups/opt_out> >> . >> >> >> > -- > 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 > h2-database+unsubscribe@**googlegroups.com<h2-database%[email protected]> > . > To post to this group, send email to [email protected]. > Visit this group at > http://groups.google.com/**group/h2-database?hl=en<http://groups.google.com/group/h2-database?hl=en> > . > For more options, visit > https://groups.google.com/**groups/opt_out<https://groups.google.com/groups/opt_out> > . > > > -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
