CODEWITHSUNDEEP
sqlsqlite
tofutim
tofutim

Reputation: 23374

What is the most efficient way to count rows in a table in SQLite?

I've always just used SELECT COUNT(1) FROM X, but perhaps this is not the most efficient. Any thoughts?

Other options include SELECT COUNT(*), or perhaps getting the last inserted id if it is auto-incremented (and never deleted).

How about if I just want to know if there is anything in the table at all? (e.g., count > 0)

Upvotes: 38

Views: 53127

Answers (6)

Larry Lustig
Larry Lustig

Reputation: 50990

The fastest way to get row counts is directly from the table metadata, if any. Unfortunately, I can't find a reference for this kind of data being available in SQLite.

Failing that, any query of the type

SELECT COUNT(non-NULL constant value) FROM table

should optimize to avoid the need for a table, or even an index, scan. Ideally, the engine will simply return the current number of rows known to be in the table from internal metadata. Failing that, it simply needs to know the number of entries in the index of any non-NULL column (the primary key index being the first place to look).

As soon as you introduce a column into the SELECT COUNT you are asking the engine to perform at least an index scan and possibly a table scan, and that will be slower.

Upvotes: 1

M Katz
M Katz

Reputation: 5436

To follow up on girasquid's answer, as a data point, I have a sqlite table with 2.3 million rows. Using select count(*) from table, it took over 3 seconds to count the rows. I also tried using SELECT count(rowid) FROM table, (thinking that rowid is a default primary indexed key) but that was no faster. Then I made an index on one of the fields in the database (just an arbitrary field, but I chose an integer field because I knew from past experience that indexes on short fields can be very fast, I think because the index is stored a copy of the value in the index itself). SELECT count(my_short_field) FROM table brought the time down to less than a second.

Upvotes: 10

Teolazza
Teolazza

Reputation: 542

If you are sure (really sure) that you've never deleted any row from that table and your table has not been defined with the WITHOUT ROWID optimization you can have the number of rows by calling:

select max(RowId) from table;

Or if your table is a circular queue you could use something like

select MaxRowId - MinRowId + 1 from
  (select max(RowId) as MaxRowId from table) JOIN
  (select min(RowId) as MinRowId from table);

This is really really fast (milliseconds), but you must pay attention because sqlite says that row id is unique among all rows in the same table. SQLite does not declare that the row ids are and will be always consecutive numbers.

Upvotes: 6

Maraduarz
Maraduarz

Reputation: 57

sp_spaceused 'table_name' (exclude single quote)

this will return the number of rows in the above table, this is the most efficient way i have come across yet.

it's more efficient than select Count(1) from 'table_name' (exclude single quote)

sp_spaceused can be used for any table, it's very helpful when the table is exceptionally big (hundreds of millions of rows), returns number of rows right a way, whereas 'select Count(1)' might take more than 10 seconds. Moreover, it does not need any column names/key field to consider.

Upvotes: -3

girasquid
girasquid

Reputation: 15526

The best way is to make sure that you run SELECT COUNT on a single column (SELECT COUNT(*) is slower) - but SELECT COUNT will always be the fastest way to get a count of things (the database optimizes the query internally).

If you check out the comments below, you can see arguments for why SELECT COUNT(1) is probably your best option.

Upvotes: 26

johnny
johnny

Reputation: 19735

I do not believe you will find a special method for this. However, you could do your select count on the primary key to be a little bit faster.

Upvotes: 0

Related Questions