Wrong result with FOUND_ROWS() in mySQL

Question

I have a total of 6 rows. When I do a query (say SELECT * from table) and I have

LIMIT 3 => FOUND_ROWS() gives 3 => 3 rows is retrieved
LIMIT 1, 3 => FOUND_ROWS() gives 4 => 3 rows is retrieved
LIMIT 2, 3 => FOUND_ROWS() gives 5 => 3 rows is retrieved
LIMIT 3, 3 => FOUND_ROWS() gives 6 => 3 rows is retrieved
LIMIT 4, 3 => FOUND_ROWS() gives 6 => 2 rows is retrieved

Any idea what is the cause of this weird behavior?

SQL QUERY

SELECT `places`.*, `category`.*, COUNT(places_reviews.place_id) AS num_reviews, (places_popularity.rating_1 + 2*places_popularity.rating_2 + 3*places_popularity.rating_3 + 4*places_popularity.rating_4 + 5*places_popularity.rating_5)/(places_popularity.rating_1 + places_popularity.rating_2 + places_popularity.rating_3 + places_popularity.rating_4 + places_popularity.rating_5) AS average_rating, FOUND_ROWS() AS num_rows FROM (`places`) JOIN `category` ON `places`.`category_id` = `category`.`category_id` LEFT JOIN `places_reviews` ON `places_reviews`.`place_id` = `places`.`id` LEFT JOIN `places_popularity` ON `places_popularity`.`place_id` = `places`.`id` WHERE `places`.`category_id` = 1 AND `places`.`name` LIKE '%%' GROUP BY `places`.`id` ORDER BY `id` desc LIMIT 3

Or in a block:

SELECT `places`.*, `category`.*, 
COUNT(places_reviews.place_id) AS num_reviews, 
(places_popularity.rating_1 + 2*places_popularity.rating_2 + 3*places_popularity.rating_3 + 4*places_popularity.rating_4 + 5*places_popularity.rating_5)/(places_popularity.rating_1 + places_popularity.rating_2 + places_popularity.rating_3 + places_popularity.rating_4 + places_popularity.rating_5) AS average_rating, FOUND_ROWS() AS num_rows FROM (`places`) 
JOIN `category` ON `places`.`category_id` = `category`.`category_id` 
LEFT JOIN `places_reviews` ON `places_reviews`.`place_id` = `places`.`id` 
LEFT JOIN `places_popularity` ON `places_popularity`.`place_id` = `places`.`id` 
WHERE `places`.`category_id` = 1 
    AND `places`.`name` LIKE '%%' 
GROUP BY `places`.`id` 
ORDER BY `id` desc LIMIT 3

Can you show the full queries? – Pekka May 10 '11 at 19:37 — Pekka, May 10 '11 at 19:37
Sure, I have posted the full SQL query in the main post – Nyxynyx May 10 '11 at 19:41 — Nyxynyx, May 10 '11 at 19:41

Paolo_Mulder · Accepted Answer · 2011-05-10T19:55:06.023

5

Edit :

This is what you are looking for : http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows

So in your query :

SELECT sql_calc_found_rows .....

edited May 10 '11 at 19:55

answered May 10 '11 at 19:41

Paolo_Mulder

1,233
1
16
28

I want to return the total results unaffected by the LIMIT clause. Will mysql_num_rows do that? – Nyxynyx May 10 '11 at 19:45

score 2 · Answer 2 · answered May 10 '11 at 19:50

Try in this way

SELECT sql_calc_found_rows `places`.*, `category`.*, 
COUNT(places_reviews.place_id) AS num_reviews, 
(places_popularity.rating_1 + 2*places_popularity.rating_2 + 3*places_popularity.rating_3 + 4*places_popularity.rating_4 + 5*places_popularity.rating_5)/(places_popularity.rating_1 + places_popularity.rating_2 + places_popularity.rating_3 + places_popularity.rating_4 + places_popularity.rating_5) AS average_rating FROM (`places`) 
JOIN `category` ON `places`.`category_id` = `category`.`category_id` 
LEFT JOIN `places_reviews` ON `places_reviews`.`place_id` = `places`.`id` 
LEFT JOIN `places_popularity` ON `places_popularity`.`place_id` = `places`.`id` 
WHERE `places`.`category_id` = 1 
    AND `places`.`name` LIKE '%%' 
GROUP BY `places`.`id` 
ORDER BY `id` desc LIMIT 3;

select found_rows();

score 1 · Answer 3 · answered May 19 '15 at 10:54

The quickest solution is to subquery your actual query like this:

SELECT SQL_CALC_FOUND_ROWS * FROM (SELECT whatever FROM whatever WHERE whatever LIMIT whatever) ax; 
select FOUND_ROWS();

Now you will get the correct results. The main reason being that SQL_CALC_FOUND_ROWS mainly tracks rows found (i.e. without LIMITS) not rows returned.

Wrong result with FOUND_ROWS() in mySQL

3 Answers3