Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to use MySQL Found_Rows() in PHP?

Tags:

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?

like image 664
murvinlai Avatar asked Aug 10 '10 21:08

murvinlai


People also ask

What is SQL_ CALC_ FOUND_ ROWS in MySQL?

MySQL has a nonstandard query modifier called SQL_CALC_FOUND_ROWS. When in use on a SELECT with LIMIT, it attempts to calculate how many rows would have been returned if the limit were not there, and then store that for later retrieval in FOUND_ROWS().

Why use SQL_ CALC_ FOUND_ ROWS?

SQL_CALC_FOUND_ROWS and FOUND_ROWS() can be useful in situations when you want to restrict the number of rows that a query returns, but also determine the number of rows in the full result set without running the query again.

What is $row in PHP?

Return Value: Returns an array of strings that corresponds to the fetched row. NULL if there are no more rows in result set. PHP Version: 5+


1 Answers

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.

like image 158
Marc B Avatar answered Sep 22 '22 00:09

Marc B