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:
如何最有效地计算一个大表中的行数?我有一个有2300万行的表，以下查询在生产硬件上花费了30多秒:
select count(*) from tablename;
It seems that MySQL must be doing a table scan, but it doesn't seem like this should be necessary.
MySQL似乎必须进行表扫描，但似乎不需要这样做。
3 个解决方案
#1
14
If an approximation is enough, you can use:
如果一个近似值足够，你可以使用:
show table status like 'tablename'
#2
3
Row count of a table is slow in InnoDB.
在InnoDB中，表的行数很慢。
MyISAM on the other hand have the row count as a property of the table, which makes your query really fast on MyISAM.
另一方面，MyISAM将行计数作为表的属性，这使得MyISAM上的查询速度非常快。
#3
0
Use cache query SELECT SQL_CACHE count(column_name) from table
使用缓存查询从表中选择SQL_CACHE count(column_name)
#1
14
If an approximation is enough, you can use:
如果一个近似值足够，你可以使用:
show table status like 'tablename'
#2
3
Row count of a table is slow in InnoDB.
在InnoDB中，表的行数很慢。
MyISAM on the other hand have the row count as a property of the table, which makes your query really fast on MyISAM.
另一方面，MyISAM将行计数作为表的属性，这使得MyISAM上的查询速度非常快。
#3
0
Use cache query SELECT SQL_CACHE count(column_name) from table
使用缓存查询从表中选择SQL_CACHE count(column_name)