Thomas, Thanks for the quick response. Here are the indexes we have on the table. Index on test1 TRACKS_IN(rec_id, side_id) Index on test2 IN_NAME3(rec_id, source) Index on test3 UPC_IN(UPC) UPC_IN5(SIDE_ID, UPC, GRE_TITLE) UPC_IN7(SIDE_ID, UPC) UPC_IN6(UPC, SIDE_ID) Index on test4 DAVE_IN_ALL2(UPC, sales_id) DAVE_IN4(UPC) There was a typo on the query I posted. Here is the corrected query. explain select *t3*.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; Let me know if you need more information. Thanks Shiva
On Friday, May 17, 2013 2:41:19 AM UTC-7, Thomas Mueller wrote: > 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] > <javascript:>>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] <javascript:>. >> To post to this group, send email to [email protected]<javascript:> >> . >> 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.
