I am 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.
