Thanks for the document , that helped me to understand the index better. I have created index idx_source on test2(source);
Now I do not see any table scan in query plan, I yet to test the query performance with large data. I will post it here if it improved the performance. Thanks for your help. Shiva On Friday, May 17, 2013 1:07:22 PM UTC-7, Thomas Mueller wrote: > > Hi, > > How would *you* execute the query, if you had to manually process it? You > couldn't start with t1, as there is no fixed condition on t1 - only a join > condition. You couldn't start with t2, as there is no index starting with > source. Maybe you read > http://h2database.com/html/performance.html#storage_and_indexes (again) > to understand how H2 can process queries. > > Regards, > Thomas > > ------------------ > > 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) > > 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; > > > > > On Fri, May 17, 2013 at 7:57 PM, Shiva <[email protected] > <javascript:>>wrote: > >> 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]> 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 h2-database...@**googlegroups.com. >>>> 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] <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.
