Great Question! I am eager to hear the response! I use a ton of JOINs and INTERSECTs. Coverage on that topic would be great too!
-----Original Message----- From: William Hachfeld [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 27, 2004 9:34 AM To: [EMAIL PROTECTED] Subject: [sqlite] Index Usage 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
