Hi,
> 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?
H2 currently only uses one index.
> And if the query is run with DESCRIPTION LIKE '%someDescription%'
> the description index isn't used either.
It is not possible to use an index in this case, if the % is at the
beginning of the text (at least not that I know of). Are other
databases faster in that case?
> 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.
Did you set the property before loading the database driver?
What is the result of EXPLAIN SELECT in this case?
> So how do I create composite col indexes that would get used in
> queries with more than one column in the where clause?
When using LIKE 'abc%' it's not possible to use a multi-column index.
Regards,
Thomas
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
