How to efficiently count rows in a query by limiting the number or found rows?

Question

I have a website where I output a pagination with a limited number of pages, let's set it to 50 for example. That is, if there are actually 51 or more pages of content the 50th will be the latest that can be viewed by the users.

If a user requests a page that goes beyond the actual limit of available pages (I know it because the page's number is more than 50 or because the query for that page returns 0 rows) I want to display the last available page instead. So in this case I'll display the 50th page if there are 50 or more pages available, or I'll just calculate how many pages that are and show the latest.

Right now to do this I count how many rows are found in the query, for example:

SELECT COUNT(*) FROM my_table WHERE codition1 > X AND condition2 > Y

The problem here is that this query may even returns a count of 200,000 rows, when I actually just need to know if there are enought rows for the 50th page, or if there are less pages I need to know what's the latest.

Sometimes this query may take quite some time to iterate across the whole database to return a number that I actually not entirelly need, so I'm assuming that just counting the first (50*rows_per_page) matched rows would be a lot more efficient.

To accomplish this I've tried:

SELECT COUNT(*) FROM my_table WHERE codition1 > X AND condition2 > Y LIMIT 50*rows_per_page

But I actually get the same result of the first query.

What's the correct way, and possibly the most efficient way, to do this?

Answer 1

First, it's almost never OK to use LIMIT without ORDER BY, because you will not get consistent results across different executions of the query. So, especially for the pagination queries, I'd use:

SELECT <list of columns>
FROM <tables>
WHERE <conditions>
ORDER BY <some columns>
LIMIT rows_per_page
OFFSET x ;

Now, to find the number of pages this query could return - and with the extra condition that you will not use more than 50 pages - I'd try a separate query;

SELECT CEIL( COUNT(*) / 50 ) AS pages
FROM
  ( SELECT NULL
    FROM <tables>
    WHERE <conditions>
    LIMIT 50 * rows_per_page
  ) AS x ;

(and that is an exception to the "never use LIMIT without ORDER BY" rule above :)