How to use MySQL Found_Rows() in PHP?

Question

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

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.

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);

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

Answer 1

Its two queries:

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

PHP can only issue a single query per mysql_query call

Answer 2

Do you really think that selecting ALL rows from tables is faster than counting them?
Myisam stores a number of records in table's metadata, so SELECT COUNT(*) FROM table don't have to access data.

Answer 3

It's a common misconception, that SQL_CALC_FOUND_ROWS performs better than COUNT(). See this comparison from Percona guys: http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/

To answer you question: Only one query is allowed per one mysql_query call, as described in manual: mysql_query() sends a unique query (multiple queries are not supported)

Multiple queries are supported when using ext/mysqli as your MySQL extension:

http://www.php.net/manual/en/mysqli.multi-query.php

Answer 4

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.

Think of how this works:

SELECT SQL_CALC_FOUND_ROWS * FROM Users;

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.

Whereas, doing:

SELECT count(*) FROM users;

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.