iPhone dev Stupidity 80: SQLite3 counting rows

From here:

SQLite does not keep count of rows in a table. So when you execute

SELECT COUNT(*) FROM table;

what SQLite does is step through table row by row and count them.

Only way to get the total number of rows in a table faster is to keep

track of it yourself. This can easily be done using insert/delete

triggers that will increment/decrement row count in a separate table.

If you just cares the row number, try: SELECT COUNT(0) FROM TABLE

it's more efficient.

and in code, you can get the count directly:

        NSString * sql = [NSString stringWithFormat: @"SELECT count(0) from %@ WHERE %@ = %d", table, colName, val];

sqlite3_stmt *compiledStatement;

if(sqlite3_prepare_v2(database_, [sql UTF8String], -1, &compiledStatement, NULL) == SQLITE_OK) {

int count = 0;

if(sqlite3_step(compiledStatement) == SQLITE_ROW){

count = sqlite3_column_int(compiledStatement, 0);

}

sqlite3_finalize(compiledStatement);

return count;

}