Most efficient way to get table row count

mysql sql count
James Simpson picture James Simpson · Jun 1, 2009 · Viewed 113.4k times · Source

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.

Answer

Graham Swan picture Graham Swan · Apr 5, 2010

Following is the most performant way to find the next AUTO_INCREMENT value for a table. This is quick even on databases housing millions of tables, because it does not require querying the potentially large information_schema database.

mysql> SHOW TABLE STATUS LIKE 'table_name';
// Look for the Auto_increment column

However, if you must retrieve this value in a query, then to the information_schema database you must go.

SELECT `AUTO_INCREMENT`
FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_SCHEMA = 'DatabaseName'
AND    TABLE_NAME   = 'TableName';

Related questions

How to get multiple counts with one SQL query?

I am wondering how to write this query. I know this actual syntax is bogus, but it will help you understand what I am wanting. I need it in this format, because it is part of a much bigger query. …

Read More
SQL: How to get the count of each distinct value in a column?

I have a SQL table called "posts" that looks like this: id | category ----------------------- 1 | 3 2 | 1 3 | 4 4 | 2 5 | 1 6 | 1 7 | 2 Each category number corresponds to a category. How would I go about counting the number of times each category appears on a post all in one …

Read More
Counting number of grouped rows in mysql

In a table xyz I have a row called components and a labref row which has labref number as shown here Table xyz labref component NDQA201303001 a NDQA201303001 a NDQA201303001 a NDQA201303001 a NDQA201303001 b NDQA201303001 b NDQA201303001 b NDQA201303001 …

Read More