What Is The Most Efficient Way to Count Rows in a Table in Sqlite

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

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.

How to count row of a table from sqlite database android

Try This

private static final String DB_TABLE_PLACES = "Places";
private SQLiteDatabase mDatabase;

private long fetchPlacesCount() {
    String sql = "SELECT COUNT(*) FROM " + DB_TABLE_PLACES;
    SQLiteStatement statement = mDatabase.compileStatement(sql);
    long count = statement.simpleQueryForLong();
    return count;
}

See More Here.

SQLite - getting number of rows in a database

If you want to use the MAX(id) instead of the count, after reading the comments from Pax then the following SQL will give you what you want

SELECT COALESCE(MAX(id)+1, 0) FROM words

Count Number of Rows in a SQLite Database

I came across a solution, using my code above, just replacing the step statement with the code below:

if (sqlite3_step(stmt) == SQLITE_ERROR) {
    NSAssert1(0,@"Error when counting rows  %s",sqlite3_errmsg(database));
} else {
    rows = sqlite3_column_int(stmt, 0);
    NSLog(@"SQLite Rows: %i", rows);
}

Is count(*) constant time in SQLite, and if not what are alternatives?

SQLite has a special optimization for COUNT(*) without a WHERE clause, where it goes through the table's B-tree pages and counts entries without actually loading the records.
However, this still requires that all the table's data (except overflow pages for large records) is visited, so the runtime is still O(n).

SQLite does not store a separate record count in the database because that would make all changes slower.

Fastest way to count exact number of rows in a very large table?

Simple answer:

• Database vendor independent solution = use the standard = COUNT(*)
• There are approximate SQL Server solutions but don't use COUNT(*) = out of scope

Notes:

COUNT(1) = COUNT(*) = COUNT(PrimaryKey) just in case

Edit:

SQL Server example (1.4 billion rows, 12 columns)

SELECT COUNT(*) FROM MyBigtable WITH (NOLOCK)
-- NOLOCK here is for me only to let me test for this answer: no more, no less

1 runs, 5:46 minutes, count = 1,401,659,700

--Note, sp_spaceused uses this DMV
SELECT
   Total_Rows= SUM(st.row_count)
FROM
   sys.dm_db_partition_stats st
WHERE
    object_name(object_id) = 'MyBigtable' AND (index_id < 2)

2 runs, both under 1 second, count = 1,401,659,670

The second one has less rows = wrong. Would be the same or more depending on writes (deletes are done out of hours here)

---

Related Topics