Try creating indexes separately.
Like this:
create index T1_IN on test1(REC_ID)
create index T2_IN on test1(SIDE_ID)
- rami
On 17.5.2013 3:39, Shiva wrote:
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.
--
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.
