I'm sharing a simple benchmark done to work out the most efficient way to count the number of rows in a table for my app.
The first thing I should point out, this will only work when you adhere to the following: * You want the total number of rows in a table (i.e. no WHERE clause) * Any deletions from the table are done ONLY from the beginning of the table Create 100,000 rows in the 'event' table like: INSERT INTO event VALUES (NULL, 1, 11, 'sometext 1', '01-01-05 12:00'); INSERT INTO event VALUES (NULL, 1, 11, 'sometext 2', '01-01-05 12:00'); INSERT INTO event VALUES (NULL, 1, 11, 'sometext 3', '01-01-05 12:00'); ... INSERT INTO event VALUES (NULL, 1, 11, 'sometext 99999', '01-01-05 12:00'); INSERT INTO event VALUES (NULL, 1, 11, 'sometext 100000', '01-01-05 12:00'); Results SQL, Attempt #1 (ms), #2 (ms), #3 (ms) SELECT count(*) FROM event, 83.49, 83.31, 83.48 SELECT count(eventID) FROM event, 109.09, 106.92, 107.98 SELECT min(eventID) FROM event, 0.64, 0.61, 0.59 SELECT max(eventID) FROM event, 0.68, 0.57, 0.57 So (for me at least), it is quicker to run two separate min/max select statements and compute the number of rows than to use the count function. An interesting fact for those that are still reading. The times for the following SQL statement: SELECT min(eventID), max(eventID) FROM event took 155.18ms, 152.08ms, 154.70ms Regards Nick Supporting Info: This was using SQLite 3.2.3 in SQLiteSpy 1.2 The table was created with: CREATE TABLE event ( eventID INTEGER PRIMARY KEY AUTOINCREMENT, elementID INTEGER NOT NULL, typeID INTEGER NOT NULL, value TEXT NOT NULL, datetime TEXT NOT NULL ); Email if you have any questions ******************************************************************** This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. ********************************************************************
