No, SQLite (as well as most other database systems) does a more elaborate evaluation. I breaks the statement apart into subterms and tries to determine which one makes the most beneficial use of an index so the order of the statement does not matter. See http://www.sqlite.org/optoverview.html for details.
regards gerd Carlos Rocha schrieb am 24.08.2011 21:12: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
