> > It seems equally logical to me that one of A or B might be evaluated, and if > it were false, then the other might not be evaluated.
I don't think so if efficiency matters. Of course the rule could be to evaluated from right to left instead, but it's good to have just one rule, and again, it seems logical to me that it should be from left to right > > And it would be logical to choose which of A or B to evaluated on a predicted > cost and probability of an advantageous false result. I don't see how predicting cost and probability could help here. rowID BETWEEN 1000000 AND 2000000 are roughly 1M, and createdAt BETWEEN '2011-08-01' AND '2011-08-02' could be 10M. In a simple case like this it's good to leave the wheel to who knows the db. > > but hay. Who said their could only be one logical approach. > > Alex > > > On 24 Aug 2011, at 20:12, Carlos Rocha wrote: > >> 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
