Igor Tandetnik schrieb am 24.08.2011 20:20: > 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.
I know of this way of forcing SQLite to not use a specific index. Forcing a specific index using INDEXED BY might as well be a solution. Unfortunately I can't rely on a specific data distribution, that's why I chose to set SQLITE_ENABLE_STAT2. I thought it would make the query analyzer choose a good plan based on the actual data distribution. regards gerd _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
