2009/9/11 Thomas Mueller <[email protected]>:
>
> Hi,
>
> Currently, the optimizer doesn't calculate the cost of using the ORDER
> BY index. It does use this index only if no other index is used. I
> know this needs to be improved...
I actually started to look into this myself before I sent the mail,
but got stuck at the point where I couldn't figure out how the H2 code
handles compound indexes like my (query_count,query).
I may have a stab at it another day, so if you have any clues to drop
it will be appreciated :-)
> Anyway, the only workaround I know
> is to trick the optimizer, for example using ('x' || query LIKE
> '_f%'):
>
> drop table suggest_index;
> CREATE TABLE suggest_index(
> query VARCHAR(250) PRIMARY KEY,
> query_count INTEGER,
> hit_count INTEGER,
> mtime BIGINT);
> CREATE UNIQUE INDEX suggest_query_count ON suggest_index(query_count, query);
> insert into suggest_index select x, x, x, x from system_range(1, 100);
> explain SELECT query, query_count, hit_count FROM suggest_index
> WHERE hit_count>0 and 'x' || query LIKE '_f%' ORDER BY query_count LIMIT 10;
Interesting, I must try that on monday. I also tried forcing it to
order by query_count first by doing a nested select in the spirit of:
SELECT *
FROM (SELECT * FROM suggest_index ORDER BY query_count)
WHERE query LIKE 'f%'
LIMIT 10
For queries where the LIKE clause only returns a small number of hits
this is actually a bit faster than my original query. If the LIKE
clause matches a lot of rows (like it would in this concrete example)
the performance really takes a hit - unsurprisingly.
--
Cheers,
Mikkel
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
