Today I have tested the query performance in our QA environment. The query executed in 4ms after creating the index, before it used to take 548ms, it is a big improvement for us.
Thanks Shiva On Monday, May 20, 2013 7:06:02 PM UTC-7, Shiva wrote: > > 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]> 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]. >>> 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.
