[quote]SELECT COUNT(*) from myTABLE // took 0.004 seconds

SELECT COUNT(8) from myTABLE // took 0.001 seconds

SQLITE database table with 643 records on OSX system

[/quote]

Well, I think you discovered a bug in the sqlite optimizer.

I am able to reproduce this result however, but actually the other way around:

CREATE TABLE integers (i integer,k integer);

INSERT INTO integers (i,k) VALUES (0,0);

INSERT INTO integers (i,k) VALUES (1,1);

INSERT INTO integers (i,k) VALUES (2,2);

INSERT INTO integers (i,k) VALUES (3,3);

INSERT INTO integers (i,k) VALUES (4,4);

INSERT INTO integers (i,k) VALUES (5,5);

INSERT INTO integers (i,k) VALUES (6,6);

INSERT INTO integers (i,k) VALUES (7,7);

INSERT INTO integers (i,k) VALUES (8,8);

INSERT INTO integers (i,k) VALUES (9,9);

INSERT INTO integers (i,k) SELECT (tenthousands.i * 10000)+(thousands.i * 1000)+(hundreds.i * 100) + (tens.i * 10) + units.i,

(tenthousands.i * 10000)+(thousands.i * 1000)+(hundreds.i * 100) + (tens.i * 10) + units.i AS x

FROM integers AS units

CROSS JOIN integers AS tens

CROSS JOIN integers AS hundreds

CROSS JOIN integers AS thousands CROSS JOIN integers AS tenthousands;

Select (*) from integers

Query time: 1 millisecond(s), Number of affected records: 100000

select count(1) from integers

Query time: 7 millisecond(s), Number of affected records: 100000

result: 100010

Well, this might just be a sqlite 3.9.1 wrinkle.