如何在PHP中使用MySQL Found_Rows()?

问题描述

由于性能问题，我尝试避免执行Count(). (即，从用户中选择COUNT()个)

I try to avoid doing Count() because of performance issue. (i.e. SELECT COUNT() FROM Users)

如果我在phpMyAdmin中运行以下命令，可以:

If I run the followings in phpMyAdmin, it is ok:

  SELECT SQL_CALC_FOUND_ROWS * FROM Users;
  SELECT FOUND_ROWS();

它将返回#行.即用户数.

It will return # of rows. i.e. # of Users.

但是，如果我在PHP中运行，则无法做到这一点:

However, if I run in in PHP, I cannot do this:

  $query = 'SELECT SQL_CALC_FOUND_ROWS * FROM Users;
    SELECT FOUND_ROWS(); ';
  mysql_query($query);

似乎PHP不希望传入两个查询.那么，我该怎么做?

It seems like PHP doesn't like to have two queries passing in. So, how can I do that?

推荐答案

SQL_CALC_FOUND_ROWS仅在使用LIMIT子句时有用，但仍想知道如果没有LIMIT.

SQL_CALC_FOUND_ROWS is only useful if you're using a LIMIT clause, but still want to know how many rows would've been found without the LIMIT.

考虑它的工作原理:

SELECT SQL_CALC_FOUND_ROWS * FROM Users;

您正在强制数据库检索/解析表中的所有数据，然后将其丢弃.即使您不打算检索任何行，DB Server仍会假设您需要这些数据，而仍会开始从磁盘中提取实际数据.

You're forcing the database to retrieve/parse ALL the data in the table, and then you throw it away. Even if you aren't going to retrieve any of the rows, the DB server will still start pulling actual data from the disk on the assumption that you will want that data.

以人为本，您购买了超级杂货店的全部物品，但从收银员的架子上扔掉了除那包口香糖以外的所有东西.

In human terms, you bought the entire contents of the super grocery store, but threw away everything except the pack of gum from the stand by the cashier.

在这样做:

SELECT count(*) FROM users;

让DB引擎知道，尽管您想知道有多少行，但您不必关心实际数据.在大多数任何智能DBMS上，引擎都可以从表的元数据中检索此计数，或者通过表的主键索引进行简单的运行，而无需触及磁盘上的行数据.

lets the DB engine know that while you want to know how many rows there are, you couldn't care less about the actual data. On most any intelligent DBMS, the engine can retrieve this count from the table's metadata, or a simple run through the table's primary key index, without ever touching the on-disk row data.

这篇关于如何在PHP中使用MySQL Found_Rows()?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！