MySQL FOUND_ROWS()

[cereal]

Hello,

so, I'm playing a bit with MariaDB 10.0.29 and I cannot understand why FOUND_ROWS() keeps returning the 1 whatever happens to the latest select query. Here's my test:

> CREATE TABLE `test` (`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, `msg` VARCHAR(100) NULL) ENGINE = InnoDB;
Query OK, 0 rows affected
Time: 0.782s

> INSERT INTO `test` (`msg`) VALUES('apples'), ('oranges'), ('strawberries'), ('cherries'), ('random');
Query OK, 5 rows affected
Time: 0.180s

> SELECT SQL_CALC_FOUND_ROWS * FROM `test`;
+------+--------------+
|   id | msg          |
|------+--------------|
|    1 | apples       |
|    2 | oranges      |
|    3 | strawberries |
|    4 | cherries     |
|    5 | random       |
+------+--------------+
5 rows in set
Time: 0.003s

> SELECT FOUND_ROWS();
+----------------+
|   FOUND_ROWS() |
|----------------|
|              1 |
+----------------+
1 row in set
Time: 0.002s

Expected result 5. The same happens with MyISAM engine.

Any clue why this happens? To avoid any possible backside issue, I have tested from a fresh connection through the command line client, but it does not seems to make difference.

The online test with MySQL 5.6, instead, returns 0, it is accessible here:

• http://sqlfiddle.com/#!9/f8f47/1

For the online test result I'm not sure it depends on SQLfiddle or it is MySQL 5.6.

[Reverend Jim]

If you are only interested in the number of rows of something then do

SELECT COUNT(*) FROM test [WHERE...]

Doing

SELECT SQL_CALC_FOUND_ROWS * FROM `test`

is a waste. In the first case, all the db has to return is the record count. In the second case it has to return all of the rows.

[Dani]

Jim, I think that cereal is just demonstrating a simple example to show that found_rows() isn't working for him, and his actual use case isn't as simplistic as in the example he demonstrated. The advantage to using found_rows() versus count(*) is that count returns a count of all rows that match the SQL query. found_rows() returns a count of all rows that match the SQL query if a LIMIT were not in place.

We use it here on DaniWeb. So, for example, we have 30 posts per page. So page one the SQL query has LIMIT 0, 30, page 2 has LIMIT 30,30, etc. If I were to just do a COUNT(*) then it would always return 30 because that's a count of how many rows it's returned. However, in order to build out our page navigation, I need to know how many rows would be returned if the LIMIT clause were not in place. Therefore, I can use cereal's method to give me the total number of rows in the database so I can accurately build my page navigation.

Which leads me to my next point. Cereal, since sql_calc_found_rows only makes sense with LIMIT, perhaps it doesn't calculate itself accurately without a LIMIT in place? Try adding LIMIT to your query and see if it returns better results?

[cereal]

Thank you Jim! Yes, that works fine and also counting the resulting array works fine.

The original query is not like in the above example: I was using FOUND_ROWS() in a PHP PDO class, to automatically extract the number of rows, but it was not working appropriately. So I started playing with an example table and added SQL_CALC_FOUND_ROWS too and came down with the above test.

Even by doing:

SELECT SQL_CALC_FOUND_ROWS * FROM `test` LIMIT 3;

then FOUND_ROWS() should return 5, instead it returns 1. In practice, I do not understand why it does not output the expected result.

I just did a test on MariaDB 10.0.28 and MariaDB 10.1.19 and returns 5, as expected. My current instead is 10.0.29, so it may be a bug.

//EDIT

@Dani, yes, I added SQL_CALC_FOUND_ROWS just to test the query.

[Dani]

Are you using a pre-release version?

[cereal]

No, it is the latest stable version available on Ubuntu 16.04. I will try to download it from MySQL and see if it makes some difference.

[cereal]

Just tested on two others Ubuntu 16.04: 32 and 64 bit, and it works fine with the same database version. So it may be my specific box.

Thank you for support Dani and Jim!