Fast number of rows in Sqlite

I have a single table in an Sqlite DB, with many rows. I need to get the number of rows (total count of items in the table).

I tried select count(*) from table, but that seems to access each row and is super slow.

I also tried 开发者_如何学Cselect max(rowid) from table. That's fast, but not really safe -- ids can be re-used, table can be empty etc. It's more of a hack.

Any ideas on how to find the table size quickly and cleanly?

Using Python 2.5's sqlite3 version 2.3.2, which uses Sqlite engine 3.4.0.

Do you have any kind of index on a not-null column (for example a primary key)? If yes, the index can be scanned (which hopefully does not take that long). If not, a full table scan is the only way to count all rows.

Other way to get the rows number of a table is by using a trigger that stores the actual number of rows in other table (each insert operation will increment a counter). In this way inserting a new record will be a little slower, but you can immediately get the number of rows.

To follow up on Thilo'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 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 my_short_field FROM table brought the time down to less than a second.