SQL Count total number of rows whilst using LIMIT

Question

I'm trying to create a simple image gallery, showing 16 images per page. I'm using LIMIT 16 to display the correct amount on the page, but if there are more than 16 rows, I want to have links at the bottom, allowing the user to navigate to the next page.

I know I could achieve the desired result by removing the limit and simply using a loop to display the first 16 items, but this would be inefficient. Obviously, COUNTING the number of rows would always = 16.

$sql .= "posts p, images im, postimages pi WHERE
    i.active = 1 
    AND pi.post_id = p.id
    AND pi.image_id = im.image_id 
    ORDER BY created_at LIMIT 16";

Can anyone suggest a more efficient way of doing it?

Thanks

score 29 · Answer 1 · answered Jul 14 '09 at 15:07

29

In MySQL:

SELECT  SQL_CALC_FOUND_ROWS
        *
FROM    posts p, images im, postimages pi
WHERE   i.active = 1 
        AND pi.post_id = p.id
        AND pi.image_id = im.image_id 
ORDER BY
        created_at
LIMIT 16;

SELECT  FOUND_ROWS();

The first query will return 16 rows, and the second query will return you the number of rows which would be returned be there no LIMIT clause in the first query.

You may use it to decide whether to show the "next" link or not.

answered Jul 14 '09 at 15:07

Quassnoi

413,100
91
616
614

+1. See also MySQL docu: [http://dev.mysql.com/doc/refman/5.6/en/information-functions.html#function_found-rows] – Wolfgang May 17 '11 at 13:19
-@Quassnoi, how do you return the results for both 1st and 2nd queries? I can only see the `FOUND_ROWS()` result. – tim peterson Feb 28 '13 at 17:48
@timpeterson: I see both. Where are you looking? – Quassnoi Feb 28 '13 at 18:30
@Quassnoi i think you're right, it seems what I'm dealing with is more a matter of getting them both to display using PHP... – tim peterson Feb 28 '13 at 20:10
1

@timpeterson the only way I've ever found to do it is to run two separate queries, if that helps you. (You should probably wrap the process in a transaction to guarantee no one else can sneak a second query in between the two) – RonLugge Mar 04 '13 at 08:05
2

@RonLugge: `FOUND_ROWS()` is session scope, other sessions don't affect it. – Quassnoi Mar 04 '13 at 08:59
Oh. I had no idea. Thanks @Quassnoi! – RonLugge Mar 05 '13 at 05:36

score 4 · Answer 2 · answered Jul 14 '09 at 15:02

4

Run a separate query with no limit that only returns the count.

answered Jul 14 '09 at 15:02

Joel Coehoorn

399,467
113
570
794

in light of the most upvoted answer, can you explain more why do you say this? – tim peterson Mar 04 '13 at 11:33
1

@timpeterson It's also a valid option. It has some issues with having to hit the database twice, but it's still a completely valid (if less 'efficient') answer. And as far as it's efficiency goes, depending on what you're doing, sometimes the top rated answer can violate DRY by giving you two different ways to get the same info. – RonLugge Mar 05 '13 at 05:38

score 3 · Answer 3 · edited Jul 14 '09 at 15:04

3

Are you trying to fully paginate, or just have this page and next page? If it's the latter you can simply LIMIT 17 and count the rows of the result. If you get 17 you need to paginate.

edited Jul 14 '09 at 15:04

Kenan Banks

207,056
34
155
173

answered Jul 14 '09 at 15:02

Ian Elliott

7,588
5
35
42

score 1 · Accepted Answer · answered Jul 14 '09 at 15:07

You need to offset your results for the given page you are on in addition to the separate query for getting the count that everyone else has mentioned.

$sql .= "posts p, images im, postimages pi WHERE
    i.active = 1 
    AND pi.post_id = p.id
    AND pi.image_id = im.image_id 
    ORDER BY created_at 
    LIMIT ". ($page - 1) * $pagesize .", ". $pagesize;

The normal warnings about SQL injection would apply here.

score 0 · Answer 5 · answered Jul 14 '09 at 15:02

0

use two queries, where one is your query and the other is:

SELECT COUNT(primary_key) FROM ... WHERE...

answered Jul 14 '09 at 15:02

KM.

101,727
34
178
212

SQL Count total number of rows whilst using LIMIT

5 Answers5

Linked