Most efficient way to get table row count

count, mysql, sql

Solution

If it's only about getting the number of records (rows) I'd suggest using:

SELECT TABLE_ROWS
FROM information_schema.tables 
WHERE table_name='the_table_you_want' -- Can end here if only 1 DB 
  AND table_schema = DATABASE();      -- See comment below if > 1 DB

(at least for MySQL) instead.

Problem

I currently have a database with over 6 million rows and growing. I currently do SELECT COUNT(id) FROM table; in order to display the number to my users, but the database is getting large and I have no need to store all of those rows except to be able to show the number. Is there a way to select the auto_increment value to display so that I can clear out most of the rows in the database? Using `LAST_INSERT_ID()` doesn't seem to work.

Original source