Thanks for the information Richard. Your explanation, along with the "Virtual Database Engine" document that I was reading when you wrote this, makes things a lot more clear. After reading Ulrik's suggestions, I decided to poke around a little bit using "EXPLAIN" to see if I could discover what SQLite would do for my two purposed queries...
I was able to see in the VDBE opcodes for my first query exactly what you are telling me - that SQLite will use the "grp" and "begin" terms only. So am I also correct in understanding that if I did: CREATE INDEX MultiColumnIndex ON Example (begin, end, grp); SELECT id FROM Example WHERE x < end AND y >= begin AND grp=g; That I would only make use of 1 of the 3 terms in the index? The VDBE opcodes for my sub-select query looked almost identical to the non- sub-select version. So I'm assuming that internally SQLite folds these together and treats them, in effect, like a single query rather than a two-part query. At the risk of trying everyone's patience, I have one more question... Can any generalizations be made about the relative performance of the following queries (again using the same example table): CREATE INDEX IndexA ON Example (grp, begin); SELECT id FROM Example WHERE grp=g AND x < end AND y >= begin; versus: CREATE INDEX IndexA ON Example (grp); CREATE INDEX IndexB ON Example (begin); SELECT * FROM Example WHERE grp=g INTERSECT SELECT * FROM Example WHERE x < end AND y >= begin; or maybe even: CREATE INDEX IndexA ON Example (grp, begin); CREATE INDEX IndexB ON Example (end); SELECT * FROM Example WHERE grp=g AND y >= begin INTERSECT SELECT * FROM Example WHERE x < end; given a large (~1,000,000 rows) table? Is the cost of creating the temporary table for the compound SELECT usually going to outweigh the benefit of using a second index? Is there any way to force the temporary table to be placed in main memory rather than on disk? -- William Hachfeld ([EMAIL PROTECTED], 651-683-3103) SGI Debugger, Object, and Performance Tools