GB schrieb am 24.08.2011 19:59:
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
GB schrieb am 25.08.2011 18:27:
Simon Slavin schrieb am 25.08.2011 02:00:
Had you thought of creating an explicit index on the rowid column, then running
ANALYZE again ?
Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
Simon Slavin schrieb am 25.08.2011 02:00:
> Had you thought of creating an explicit index on the rowid column, then
> running ANALYZE again ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
>
>
> 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,
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
On 24 Aug 2011, at 11:18pm, GB wrote:
> Well, that is exactly what the sqlite_stat2 table is meant for. It's
> information is supposed to make the query planner able to decide upon
> the usefulness of an index. Unfortunately, histogram information is not
> collected for the implicit rowid
Simon Slavin schrieb am 24.08.2011 23:33:
> On 24 Aug 2011, at 9:59pm, GB wrote:
>
>> Simon Slavin schrieb am 24.08.2011 22:38:
>>
>>> SELECT itemID FROM t WHERE itemID>= 100 AND itemID<= 200 AND
>>> createdAt>= '2011-08-01' createdAt<= '2011-08-02'
>> Thank you for your thoughts but I
On 24 Aug 2011, at 9:59pm, GB wrote:
> Simon Slavin schrieb am 24.08.2011 22:38:
>
>> SELECT itemID FROM t WHERE itemID>= 100 AND itemID<= 200 AND
>> createdAt>= '2011-08-01' createdAt<= '2011-08-02'
>
> Thank you for your thoughts but I already tried this with no different
> results
Simon Slavin schrieb am 24.08.2011 22:38:
> On 24 Aug 2011, at 6:59pm, GB wrote:
>
>> SELECT itemID FROM t WHERE itemID BETWEEN 100 AND 200 AND
>> createdAt BETWEEN '2011-08-01' AND '2011-08-02'
> Just out of curiosity, try changing both the BETWEEN formulations so it says
>
> SELECT
On 24 Aug 2011, at 6:59pm, GB wrote:
> SELECT itemID FROM t WHERE itemID BETWEEN 100 AND 200 AND
> createdAt BETWEEN '2011-08-01' AND '2011-08-02'
Just out of curiosity, try changing both the BETWEEN formulations so it says
SELECT itemID FROM t WHERE itemID >= 100 AND itemID <=
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
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 100 AND 200 AND
>> createdAt BETWEEN '2011-08-01' AND '2011-08-02'
>>
>> the analyzer always chooses the rowid index which
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
On 8/24/2011 1:59 PM, GB wrote:
> When perfoming a Statement like this:
>
> SELECT itemID FROM t WHERE itemID BETWEEN 100 AND 200 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
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
15 matches
Mail list logo
