On Feb 23, 2009, at 7:14 AM, Thomas Briggs wrote:
> Why do you need the 7 single-column indexes? Do you ever do a
> lookup on a single column? Bear in mind that only 1 index is used per
> query, so having seven separate indexes on seven separate columns
> means that six are always unused.
The statement above was true from the very beginning of SQLite right
up until last month. Beginning with SQLite version 3.6.8
(2009-01-12), SQLite might use multiple indices per table to help
resolve a query that includes OR terms in the WHERE clause.
Example:
CREATE TABLE t1(x,y);
CREATE INDEX t1i1 ON t1(x);
CREATE INDEX t1i2 ON t1(y);
SELECT * FROM t1 WHERE x=5 OR y=6;
The SELECT statement above will use both indices. Conceptually, the
query is converted into something more like this:
SELECT * FROM t1 WHERE rowid IN (
SELECT rowid FROM t1 WHERE x=5
UNION
SELECT rowid FROM t1 WHERE y=6
);
I say it is "conceptually converted" because really no such conversion
occurs. But the idea behind the processing is similar.
D. Richard Hipp
[email protected]
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
