You have a query and you want to display the results on a web page but because there are so many results you want to paginate the data so the user can have some links like "prev page, page 1, page 2, next page, last page" that you can see on a lot of sites these days. This is a common problem a web developer faces, it's not hard to solve but it is often not solved in the best way.

The pagination concept is based on the fact that you can retrieve just part of the results using a limit clause in the query and display them on a page. This usually makes the query faster and allows the user to easily navigate without crashing his browser or having to scroll long pages.

If you want to show the user the total number of results or you want to allow them to skip right to the last page then you need to count the total number of results that the query would return without the LIMIT clause.

How some do it?

I have seen some badly designed software that was just removing the LIMIT from the query running it and then calling mysql_num_rows() to count the rows. That may be ok if your table has just a few rows and the query returns quickly but if your table will grow to a few thousand rows or if your query joins several big tables you're going to get in troubles

So how can this be done better?

There is no way that would be best for any case but here is what you can do:

1. if your query is simple enough to not use the "group by" or "having" clause  you can simply remove all fields in your query and replace them with "count(*)" this will be really fast especially if you have the right indexes set on the table(s) in the query
2. if your query does use "group by" then modify the query to use SQL_CALC_FOUND_ROWS.

Here is an example of the second option that may be more general as it works with any query and I think it's preferable even if it may be slower then count(*)

We have this query:

you would use that to display a list of ages and how many users have a certain age in your table, you want the list to have 10 results / page and your table is really big so it's very likely you will have more then one page to display.

As you can see this query already has a "count" and "group by" in it so you can't use count to get the total number of results.

If we modify this query like this:

the query will return the exact results as the previous one but now if we do this :

we will get the total number of rows that the last query would have returned without the LIMIT clause.

This is a lot faster then running the query without the limit and counting the results with mysql_num_rows because MySQL will to the counting internally and will not have to return the whole result set to the client .

Other ideas to improve performance

Fetch details for a record in separate queries. Let's say you have a query that joins several tables and you want to display details from all those tables in a single row in your list. The joins make your query slow because it will have to examine a lot of rows when doing the count .Try to remove as many of those joins as you can do the count and then for each row in your list just run separate queries to get the other details.This way you will examine just a few rows from the other tables because you'll do the extra queries only for the results you are currently showing on a page.

Enable mysql slow query log then watch it to see how long your queries take and how many rows are examined.

Use explain to see if your query is using the right indexes and create indexes where you think they will improve the performance. If the explain will show the query will use a temporary table make sure your temporary table can hold all data in memory, if you have enough
( check the tmp_table_size and max_heap_table_size variables )

Enable query cache so the server will just server the results from cache instead of doing all the work over and over for data that is unchanged.

There are a few other techniques I have found on the official mysql documentation site, but these presented here helped me a lot in working with lists and counting the results.

If you have other tips I'll be happy to see them in the comments.