On 8/24/2011 1:59 PM, GB wrote: > 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.
I don't know the answer to your question, but if you are interested in a workaround, you can write WHERE +itemID BETWEEN 1000000 AND 2000000 The unary plus suppresses the use of index on this column. -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
