Don't know how SQLite should behave in this case, but seems logical to me that A and B would force that A is always evaluated, and B is evaluated only if A is true. I would change the order of the two betweens:
SELECT itemID FROM t WHERE createdAt BETWEEN '2011-08-01' AND '2011-08-02' AND itemID BETWEEN 1000000 AND 2000000 > Hi all, > > I have a table like this: > > CREATE TABLE t (itemID INTEGER PRIMARY KEY, createdAt DATETIME); > CREATE INDEX createIdx on t(createdAt); > > SQLite is compiled using SQLITE_ENABLE_STAT2 and table is ANALYZEd with > current content. > > When perfoming a Statement like this: > > SELECT itemID FROM t WHERE itemID BETWEEN 1000000 AND 2000000 AND > createdAt BETWEEN '2011-08-01' AND '2011-08-02' > > the analyzer always chooses the rowid index which results in a scan over > one million rows. It would have to scan only a few dozen rows if it > chose createIdx instead (which is also a covering index). Looking at the > sqlite_stat2 table shows that there is no data for the rowid index. > Could this be the reason for the suboptimal query plan? The choice works > as expected if itemID is a regular column with an index on it. > > regards > gerd > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
