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
