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?

By the way, you can't send 2 queries using `mysql_query()`. You have to send each query individually. Use another database interface if you want to send more than one at a time. — Buttle Butkus, Nov 08 '13 at 20:12

score 28 · Accepted Answer · answered Aug 11 '10 at 03:50

28

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.

answered Aug 11 '10 at 03:50

Marc B

356,200
43
426
500

Absolutely right, but if I have grouped my users and want to count how many groups are now to paginate by those groups? This is simple example I have much more complicated group in real life. – Radamanf Apr 10 '13 at 15:58
1

I have a query that is containing `limit` clause. Now I want to select both limited data and the number of total matched results using `found_rows()`. How? I want something like [this](http://stackoverflow.com/questions/33889922/how-to-get-the-number-of-total-results-when-there-is-limit-in-query#33889982) *(this doesn't work)* – Shafizadeh Nov 24 '15 at 12:31
Yes, the use case for this is of course `SELECT SQL_CALC_FOUND_ROWS * FROM users LIMIT 10;` followed by `SELECT FOUND_ROWS();` in which case the RDBMS is calculating the total pre-limit. The advantage is not having to re-write your entire query for both the count and the read. – Sam_Butler May 14 '19 at 14:39

score 23 · Answer 2 · answered Aug 10 '10 at 22:02

23

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

answered Aug 10 '10 at 22:02

JochenJung

7,183
12
64
113

Yes murvinlai, you need to execute 2 queries. But in PHP you need see that you can have N users on line sendind requests, so if other SQL execution occurs in the same conection, your FOUND_ROWS() isn't work, you need to do in way to your instructions accurs in the same conection and in sequence. One way to do this, is to execute the commands in one isolated conection, You take one conection from your fabric, execute the queries and releases to fabric. – Roberto Novakosky Feb 25 '19 at 13:53

score 5 · Answer 3 · answered Aug 10 '10 at 22:21

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

score 4 · Answer 4 · answered May 05 '15 at 23:20

Only this code works for me so i want to share it for you.

$Result=mysqli_query($i_link,"SELECT SQL_CALC_FOUND_ROWS  id From users LIMIT 10"); 
$NORResult=mysqli_query($i_link,"Select FOUND_ROWS()"); 
$NORRow=mysqli_fetch_array($NORResult); 
$NOR=$NORRow["FOUND_ROWS()"]; 
echo $NOR;

score 3 · Answer 5 · answered Oct 16 '14 at 09:41

Use 'union' and empty columns:

$sql="(select sql_calc_found_rows tb.*, tb1.title
      from orders tb
           left join goods tb1 on tb.goods_id=tb1.id
      where {$where}
      order by created desc
      limit {$offset}, {$page_size})
      union
      (select found_rows(), '', '', '', '', '', '', '', '', '')
     ";
$rs=$db->query($sql)->result_array();
$total=array_pop($rs);
$total=$total['id'];

Amin Adel · Answer 6 · 2018-12-02T10:38:25.557

3

This is an easy way & works for me :

$query = "
SELECT SQL_CALC_FOUND_ROWS * 
FROM tb1
LIMIT 5";

$result = mysqli_query($link, $query);

$query = "SELECT FOUND_ROWS() AS count";
$result2 = mysqli_query($link, $query);

$row = mysqli_fetch_array($result2);

echo $row['count'];

edited Dec 02 '18 at 10:38

answered Sep 07 '15 at 22:10

Amin Adel

970
3
17
33

score 0 · Answer 7 · answered Aug 10 '10 at 22:03

0

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.

answered Aug 10 '10 at 22:03

Naktibalda

13,705
5
35
51

How to use MySQL Found_Rows() in PHP?

7 Answers7

Linked