从mysql数据库计数行的最佳方法

问题描述：

在使用mysql查询遇到缓慢的加载时间问题后，我现在查找计数行数的最佳方法。我已经愚蠢地使用 mysql_num_rows（）函数来做到这一点，现在意识到这是一个最糟糕的方式做到这一点。
我实际上是做一个分页在PHP中的页面。
我发现了几种方法来计算行数。

After facing a slow loading time issue with a mysql query, I'm now looking the best way to count rows numbers. I have stupidly used mysql_num_rows() function to do this and now realized its a worst way to do this. I was actually making a Pagination to make pages in PHP. I have found several ways to count rows number. But I'm looking the faster way to count it.

表类型是MyISAM

The table type is MyISAM

所以问题现在是

这是最好的，更快的计数 -

Which is the best and faster to count -

1. `SELECT count(*) FROM 'table_name'`

2. `SELECT TABLE_ROWS
FROM INFORMATION_SCHEMA.TABLES WHERE table_schema =  'database_name'
AND table_name LIKE  'table_name'`

3. `SHOW TABLE STATUS LIKE 'table_name'`

4. `SELECT FOUND_ROWS()`

如果有其他更好的方法，请让我知道他们。
如果可能请描述一下答案 - 为什么它是最好的和更快。

If there are others better way to do this, please let me know them as well. If possible please describe along with the answer- why it is best and faster. So I could understand and can use the method based on my requirement.

谢谢。

答

引用 COUNT 上的MySQL参考手册>

COUNT（*）经过优化，如果SELECT从一个表中检索
，，并且没有WHERE
子句。例如：

COUNT(*) is optimized to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause. For example:

mysql> SELECT COUNT(*) FROM student;

此优化仅适用于
MyISAM表，因为为此
存储引擎存储了精确的行计数，并且可以非常快地访问。对于事务
存储引擎（如InnoDB），存储确切的行计数更多是
，因为可能发生多个事务，每个
可能影响计数。

This optimization applies only to MyISAM tables only, because an exact row count is stored for this storage engine and can be accessed very quickly. For transactional storage engines such as InnoDB, storing an exact row count is more problematic because multiple transactions may be occurring, each of which may affect the count.

同时阅读此问题
MySQL - 复杂性：SELECT COUNT（*）FROM MyTable;

从mysql数据库计数行的最佳方法

相关推荐