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?