Hi. H2 seems to be exactly what I need: fast, standalone,
embeddable, encrypted db. Excellent!
Most of my queries are extremely fast but some are so slow that I
can't possibly use them as they are.
There are obviously a few things I don't understand and would really
appreciate your help on this.
Indexing seems strange in H2. I have indexes on 3 cols. If the where
clause contains 1 indexed col its fine, but not more. If I create
another index with both cols it makes no diff.
eg.
explain select item_id, item_number, description
from item
where description like 'someDescription%'
and item_number like '0001%'
SELECT ITEM_ID, ITEM_NUMBER, DESCRIPTION
FROM PUBLIC.ITEM /* PUBLIC.IX_ITEM_1: DESCRIPTION >= 'someDescription'
AND DESCRIPTION < 'someDescriptioo' */
WHERE (DESCRIPTION LIKE 'someDescription%') AND (ITEM_NUMBER LIKE
'0001%')
Why aren't both indexes, or the index of both fields combined, being
used?
And if the query is run with DESCRIPTION LIKE '%someDescription%'
the description index isn't used either.
Another thing is using a where clause with IN.
SELECT item_number, item_id
from ITEM
WHERE item_id = (10394) -- 31ms to 50ms
--WHERE item_id IN (10394) -- 4400ms to 4600ms
-- WHERE item_id IN (SELECT ... ) -- this is how I want to use it.
I've tried using -Dh2.optimizeInJoin=true as well as
System.setProperty("h2.optimizeInJoin", "true"); with embedded and
server mode access. It makes no difference.
Queries that join 3 tables and compare values of a text field in each
to a given parameter is extremely slow! When I merged the three
tables into a temp table and queried the indexed field it returned in
250ms instead of 30 minutes.
So how do I create composite col indexes that would get used in
queries with more than one column in the where clause?
How can I optimize queries/indexes/db parameters to efficiently
execute nested queries?
The queries execute very quickly on SQL Server and PostgreSQL (and
even Access?!?), so I don't think the query structure is actually the
issue.
Thanks
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en
-~----------~----~----~----~------~----~------~--~---
