Hi,
I am trying to optimize the suggestion engine in the Summa search
engine (the small box with possible completions that pops up when you
start typing). I am seeing some suspicious behavior from the query
optimizer and could need some help. Namely it appears that H2 is
prioritizing selecting by the primary key even though selection by an
index could optimize an ORDER BY. We really want an index sort because
the result sets might be very big for short strings to complete on.
We want to find all possible completions of 'foo' sorting the results
by the query_count column. The table looks as follows:
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);
A query for all completions of 'f' might look like:
SELECT query, query_count, hit_count
FROM suggest_index
WHERE query LIKE 'f%'
ORDER BY query_count
LIMIT 10;
Giving a query plan (note that it is not index sorted):
PLAN: SELECT QUERY, QUERY_COUNT, HIT_COUNT
FROM PUBLIC.SUGGEST_INDEX /* PUBLIC.PRIMARY_KEY_9: QUERY >= 'f' AND
QUERY < 'g' */
WHERE QUERY LIKE 'f%'
ORDER BY 2
LIMIT 10
If I instead choose another column than 'query' to select on, fx.
'hit_count', the index is indeed used for sorting. Eg:
SELECT query, query_count, hit_count
FROM suggest_index
WHERE hit_count > 1 AND hit_count < 1000
ORDER BY query_count
LIMIT 10;
Gives the plan (indeed index sorted):
PLAN: SELECT QUERY, QUERY_COUNT, HIT_COUNT
FROM PUBLIC.SUGGEST_INDEX /* PUBLIC.SUGGEST_QUERY_COUNT */
WHERE (HIT_COUNT > 1) AND (HIT_COUNT < 1000)
ORDER BY 2
LIMIT 3
/* index sorted */
Of course this query is not what we want... I also tried tricking the
query optimizer into using the suggest_query_count index by inserting
a superfluous "query_count > 1" into the original query, but no luck.
So two related questions:
- Is this a bug in the H2 query optimizer, or am I out on a limp?
- Is there some clever indexing that will allow me to do this, or am
I destined to fail?
--
Cheers,
Mikkel
PS: This is on H2 1.0.109, a bit old I know, but I don't see anything
related to this in the change log, so I haven't tested the newest H2
just yet.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
