logical?
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.
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.
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
