Re: H2 query and index

[Rami Ojares]

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 h2-database...@googlegroups.com.
> To post to this group, send email to h2-da...@googlegroups.com.
> Visit this group at http://groups.google.com/group/h2-database?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>

[Thomas Mueller]

Hi,

A combined index is sometimes better, see http://h2database.com/html/performance.html#storage_and_indexes

Regards,

Thomas

On Fri, May 17, 2013 at 10:23 AM, Rami Ojares <rami....@gmail.com> wrote:

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 h2-database+unsubscribe@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
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 h2-database+unsubscribe@googlegroups.com.