William Hachfeld wrote:
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?
Correct. Specifically the y>=begin term would be used.
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.
Yes. Subqueries are folded into the main query where possible. This
optimization is necessary to implement views efficiently. A view is
really just an alias for a subquery.
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?
INTERSECT creates two temporary tables, not one. I'm guessing the
first query would be faster. But that is only a guess. Try it and
see what you get.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
