什么是使用php获取mysql表中记录总数的最佳方法？

Whats the most efficient way of selecting total number of records from a large table? Currently, Im simply doing

什么是从大表中选择记录总数的最有效方法?目前,我只是在做

$result = mysql_query("SELECT id FROM table");
$total = mysql_num_rows($result)

I was told this was not very efficient or fast, if you have a lot of records in the table.

如果表中有很多记录,我被告知这不是很有效或快速。

10 个解决方案

#1

You were told correctly. mysql can do this count for you which is much more efficient.

你被正确地告知了。 mysql可以为你做这个更有效率的计数。

$result = mysql_query( "select count(id) as num_rows from table" );
$row = mysql_fetch_object( $result );
$total = $row->num_rows;

#2

You should use SQL's built in COUNT function:

您应该使用SQL内置的COUNT函数:

$result = mysql_query("SELECT COUNT(id) FROM table");

#3

MyISAM tables already store the row count

MyISAM表已存储行计数

SELECT COUNT(*) FROM table

on a MyISAM table simply reads that value. It doesn't scan the table or the index(es). So, it's just as fast or faster than reading the value from a different table.

在MyISAM表上只读取该值。它不扫描表或索引。因此,它与从不同表中读取值一样快或更快。

#4

According to the MySQL documentation this is most efficient if you're using a MyISAM table (which is the most usual type of tables used):

根据MySQL文档,如果您使用MyISAM表(这是最常用的表类型),这是最有效的:

$result = mysql_query("SELECT COUNT(*) FROM table");

Otherwise you should do as Wayne stated and be sure that the counted column is indexed.

否则你应该按照Wayne的说法进行操作,并确保将计数列编入索引。

#5

Can I just add, that the most "efficient" way of getting the total number of records, particularly in a large table, is to save the total amount as a number in another table. That way, you don't have to query the entire table everytime you want to get the total.

我可以补充一点,获得记录总数的最“有效”方式,特别是在大表中,是将总量保存为另一个表中的数字。这样,您不必在每次想要获得总数时查询整个表。

You will however, have to set up some code or Triggers in the database to increase or decrease that number when a row is added/deleted.

但是,您必须在数据库中设置一些代码或触发器,以在添加/删除行时增加或减少该数字。

So its not the easiest way, but if your website grows, you should definitely consider doing that.

所以它不是最简单的方法,但如果你的网站增长,你绝对应该考虑这样做。

#6

Even though I agree to use the built-in functions, I don't really see any performance difference between mysql_num_rows and count(id). For 25000 results, same performance (can say exact.) Just for the record.

即使我同意使用内置函数,我也没有看到mysql_num_rows和count(id)之间有任何性能差异。对于25000个结果,相同的性能(可以说确切。)仅供记录。

#7

What about something like this:

这样的事情怎么样:

$result = mysql_query("SELECT COUNT(id) AS total_things from table");
$row = mysql_fetch_array($result,MYSQL_ASSOC);
$num_results = $row["total_things"];

#8

Just wanted to note that SHOW TABLE STATUS returns a Rows column, though I can't speak to its efficiency. Some light Googling turns up reports of slowness in MySQL 4 over two years ago. Might make for interesting time trials.

只是想注意SHOW TABLE STATUS返回一个Rows列,虽然我不能说它的效率。一些轻量级的谷歌搜索引发了两年前MySQL 4缓慢的报道。可能会进行有趣的时间试验。

Also note the InnoDB caveat regarding inaccurate counts.

另请注意InnoDB有关不准确计数的警告。

#9

Use aggregate function. Try the below SQL Command

使用聚合函数。尝试以下SQL命令

$num= mysql_query("SELECT COUNT(id) FROM $table");

#10

mysqli_query() is deprecated. Better use this:

mysqli_query()已弃用。更好地利用这个:

$result = $dbh->query("SELECT id FROM {table_name}");
$total = $result->num_rows;

Using PDO:

$result = $dbh->query("SELECT id FROM {table_name}");
$total = $result->rowCount();

(where '$dbh' = handle of the db connected to)

(其中'$ dbh'=数据库的句柄连接到)

#1