William Hachfeld wrote:
Hi,
Have a question for everyone regarding index usage in SQLite... Say that I have
the following database schema:
CREATE TABLE Example (
id INTEGER PRIMARY KEY,
grp INTEGER,
begin INTEGER,
end INTEGER
);
and I want to perform the following query:
SELECT id FROM Example WHERE grp=g AND x < end AND y >= begin;
on a large number of rows (say around one million) for some group 'g' and an
interval '[x, y)'. And, of course, with the assumption that (end > begin) for
all rows. Will my query performance be substantially improved by creating an
index such as:
CREATE INDEX MultiColumnIndex ON Example (grp, begin, end)
or will the operators "<" and ">=" prohibit SQLite from using the index?
SQLite uses inequalities in WHERE clause terms, but only for the
right-most used term of an index. So in the case above, SQLite
will use the grp and begin columns of the index and ignore the end
column. So the index
CREATE INDEX multi ON Example(grp,begin)
would work just as well as the one that includes the third "end" column.
Also, I'm aware that SQLite supports multi-column indicies, but not the use of
multiple indicies per query. Is it possible to get around the later restriction
by expressing my above query using a sub-select:
SELECT id FROM (SELECT * FROM Example WHERE grp=g)
WHERE x < end AND y >= begin;
and then creating the following indicies instead:
CREATE INDEX GroupIndex ON Example (group)
CREATE INDEX IntervalIndex ON Example (begin, end)
And if so, can any generalizations be made regarding the performance of using
the two indicies versus the first, single, index? How about disk usage?
This won't help any.
Interval conditions are recognized as long as the same column
is used in both terms. For example, this would help:
... WHERE y>=begin AND y<end
But when you have separate columns on the >= and the < terms,
SQLite has to choose one or the other, it cannot use both.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
