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... 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;
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
-~----------~----~----~----~------~----~------~--~---
