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
