Fastest way to get COUNT(*)

I have found that a simple

SELECT COUNT(*) as cnt FROM myTable

Can take a LONG TIME (with a large table)… if the statistics are not up to date.
And that even trying to update the stats before trying to get the count can take a long time

ANALYZE;

Anyone know of any tricks (SQLite)…

From the sqlite page
Statistics gathered by ANALYZE are not automatically updated as the content of the database changes. If the content of the database changes significantly, or if the database schema changes, then one should consider rerunning the ANALYZE command in order to update the statistics.

So analyze etc isn’t going to be fast not automagically fast

However I’d try count(1) instead of count(*)

[quote=330814:@Norman Palardy]From the sqlite page
Statistics gathered by ANALYZE are not automatically updated as the content of the database changes. If the content of the database changes significantly, or if the database schema changes, then one should consider rerunning the ANALYZE command in order to update the statistics.

So analyze etc isn’t going to be fast not automagically fast

However I’d try count(1) instead of count(*)[/quote]

Norman… I know the COUNT(*) vs COUNT(1) “trick” as I have advocated it here many times myself…
However it seems to make no difference… a table with 1 million records takes almost 30 seconds just to get the count,
And I realize that stats get “stale”… my point here was that

ANALYZE;
SELECT COUNT

was no faster than just

SELECT COUNT

UNLESS the stats had already been collected previously

Why not do analyze at app shut down (or other non obtrusive time) if data has been added or deleted during a session?

  • Karen

that has been added as an option… but it is not fast…
and due to the nature of the app it is not possible to track what if any tables where added, dropped, or modified

SELECT COUNT(*) FROM million;  // takes 3 minutes 21 seconds with no stats
SELECT COUNT8) FROM million;  // takes 2 minutes 11 seconds with no stats, so it was "faster"
ANALYZE MILLION; // takes 2 minutes 4 seconds to collect stats, but doesn't seem to store them??? :(

the only way it seems to store the stats if is I do just “ANALYZE;” which then does ALL the tables in the database

and then for some reason it doesn’t seem to use them… :frowning: because I know before IT DID

It seems based on content from the web that SQLite does a full tablescan for COUNT regardless of ANALYZE

per the Author of SQLite

and his Trigger suggestion is not an option, as the database(s) in question do not belong to me

from my experience, postgres is way faster then sqlite.
if you have big row count databases, better design them on a localhost postgres.

I have a sqlite db with 400k records in it, and count is not that slow, but a search with %like% is too slow for me.

Assuming the table has an Auto-Incremented Integer primary key, maybe it would be faster to do:

SELECT Count(Key0) FROM Milion WHERE Key0 > 0

as that might just use the index?

  • karen

Option of changing engines not available…
This is an SQLite Database Manager … so the database(s)/table(s) do not belong to me, therefore my app can only change them based on the users input… And because it is specifically an SQLite manager… another database engine isn’t a solution

can’t make any such “assumptions”… I
but as an educational exercise I will try it … because if in fact it is faster… I can analyze the table and choose which method to use

SELECT Count(Key0) FROM Milion WHERE Key0 > 0
took 2 min 42 seconds… so while not the slowest, not the fastest either

I just did a select count(_rowid_) from test on a table with 8 million records. It took 0.987 seconds.

SQLite??? I can’t get a million record file below 2 min 11 seconds no matter what I do

Yes it was a test SQLite database with only one table 5 columns wide and 8 million records. It had a Integer auto increment field as well as 4 text fields.

I just created a new test table with 4 text fields and no integer id field. It took 14.403 seconds to insert 8 million rows from another table in the same database. Running a SELECT count(*) FROM Test2 took only 350 ms.

I’m trying to figure out what could possible be taking over 2 minute for a select count. What is the total size of the database file?

[quote=330841:@Neil Burkholder]I just created a new test table with 4 text fields and no integer id field. It took 14.403 seconds to insert 8 million rows from another table in the same database. Running a SELECT count(*) FROM Test2 took only 350 ms.

I’m trying to figure out what could possible be taking over 2 minute for a select count. What is the total size of the database file?[/quote]
18.3 gig basically I took “Eddies Electronics” and added 2 tables with a million rows each

I have worked mostly with Oracle in the past, and it kept a record count indicator… making it very fast…

FYI… I did the same thing directly from the TERMINAL and it took about as long it seemed

What is the table schema? Any triggers, views, or indexes? I’m really curious now.

Create Table million ( id int,
firstname text,
 lastname text,
  address text,
     city text,
    state text,
      zip text,
    phone text,
    email text,
   photo,
  taxable int)

no PK, No Index, No Trigger

In the end, does it matter?

does what matter? 0.5 seconds vs 136 seconds? yeah, that matters

Ok… my app using Neils table returns Count in 0.07 seconds
my table which is similar in structure takes over 2 minutes

and this only would solve PART of the problem
The problem ultimately is how to page thru that 1M+ record table?
I THOUGHT I could use OFFSET and LIMIT… but that won’t work, because SQLite reads ALL the records up to the OFFSET before emitting them to the recordset… So the farther down you page, the slower it gets… which saves nothing in the long run…or makes the lag way worse…

Any ideas?

Would be great if I could “pause” the recordset from being completely created…