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.
