Re: How to Force Index on H2 ?

[Thomas Mueller]

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 <write...@gmail.com> 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 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.
 
 

[Shiva]

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

[Thomas Mueller]

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;

[Shiva]

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

[Shiva]

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