Hi, Well, you didn't include information about the tables and (other) indexes, so there is no way to help really.
Regards, Thomas On Fri, May 17, 2013 at 2:31 AM, Shiva <[email protected]> wrote: > I was 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 [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. > > > -- 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.
