GB schrieb am 24.08.2011 19:59:
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
sorry for replying to myself, but could someone knowledgeable about the
query planner statistics comment on this? It would be nice to have the
"best of two worlds" with direct rowid lookups and fair weighted index
seeks. Are the missing statistics for the rowid tree an omission or
would adding this have a broader impact on the code?
regards
gerd
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
