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?
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?
Thanks in advance for any information regarding the above!
-- William Hachfeld
