CODEWITHSUNDEEP
mysql
Landon Kuhn
Landon Kuhn

Reputation: 78421

MySQL: How to efficiently count the number of rows in a large table?

What is the way to most efficiently count the total number of rows in a large table? I have a table with 23 million rows and the following query takes 30+ seconds on production hardware:

select count(*) from tablename;

It seems that MySQL must be doing a table scan, but it doesn't seem like this should be necessary.

Upvotes: 15

Views: 6513

Answers (3)

Ajeet Singh
Ajeet Singh

Reputation: 32

Use cache query SELECT SQL_CACHE count(column_name) from table

Upvotes: 0

tsg
tsg

Reputation: 2041

If an approximation is enough, you can use:

show table status like 'tablename'

Upvotes: 21

Emil Vikström
Emil Vikström

Reputation: 91902

Row count of a table is slow in InnoDB.

MyISAM on the other hand have the row count as a property of the table, which makes your query really fast on MyISAM.

Upvotes: 3

Related Questions