We have the following table (in SQLite on Android) which a tree structure (Nested Set model) of words and their frequencies:
lexikon
-------
_id integer PRIMARY KEY
word text
frequency integer
lset integer UNIQUE KEY
rset integer UNIQUE KEY
And the query:
SELECT word
FROM lexikon
WHERE lset BETWEEN @Low AND @High
ORDER BY frequency DESC
LIMIT @N
I suppose a covering index on (lset, frequency, word)
would be useful but I feel it may not perform well if there are too many lset
values in the (@High, @Low)
range.
A simple index on (frequency DESC)
may also be sufficient sometimes, when a search using that index yields early the @N
rows that match the range condition.
But it seems that performance depends a lot on the parameter values.
Is there a way to make it perform fast, regardless of whether the range (@Low, @High)
is wide or narrow and regardless of whether the top frequency words are luckily in the (narrow) selected range?
frequency
column? – Jack Douglas May 23 '12 at 8:59