Curious Timing Issue

Databases
1

If I do

SELECT count(*) FROM MILLION

if takes like 2 minutes to return the answer (which is 1,000,000)
During that time is seems to automatically execute the ANALYZE command, since SQLITE_STAT1 appears when the above query completes

Subsequent calls to the same above query return < 1/2 second… makes sense… it has the stats…

Yet, if I DROP the SQLITE_STAT1 table, and manually run the ANALYZE command it , that runs in 0.60 seconds
and the COUNT takes about the same… for a total of 1.2 seconds

So anyone know why the COUNT without a previous Analyze takes 100x longer? I would have expected on the order of 1.2 seconds (the combined of doing the Analyze followed by COUNT

2

No, but a couple of questions…
Do you get the same result from

select count(1)

Is there an index

and if so, does
select count(nameofindexfield) work any faster?

3

there is no index
under the same circumstances above

select count(8) from million

vs

select count(*) from million

was nearly identical

and before anyone asks :slight_smile: “Why don’t you have an index on a table with 1,000,000 record?!!!”
it is because this is a stress test table :slight_smile: