Best way to implement pagination

PHP
1

Usually I use the page to determine the limits for the query, so let’s say if we’re on page 3 and we have 15 items per page I’ll add a ‘LIMIT 30, 45’ in the query.

When you build pagination links you need to know the total number of products so you know how many pages you’ll need to link to ( example: 78 pages, 15 products per page = 6 pages ) but you need to run another time the same query without ‘LIMIT 30, 45’ to know how many products you’re retrieving.

So I could just retrieve all the records and limit the output with PHP to those 15 of the current page.

retrieving products info requires joining more than a single table, which method is more efficient on a potentially high traffic site?

2

I didn’t know about SQL_CALC_FOUND_ROWS, it’s exactly what I needed.
thank you a lot kyberfabrikken

thank you AnthonySterling as well for sharing your idea

3

Ah, a much better solution.

My offering falls down when you implement a WHERE clause (unless you repeat it in the sub query), thanks for the heads up Kyberfabrikken. :cool:

4

You certainly don’t want to pull everything into php and do the limiting there, unless for very special cases. The extra query is a slight overhead, but it shouldn’t matter much. Normally, the database would cache the result between the two queries, so the only wasted effort is the network traffic between php and the database.

In some cases, you can improve performance by using SQL_CALC_FOUND_ROWS

5

You could just use a sub query to obtain the number of records.


SELECT
    id
  , type
  , (SELECT COUNT(*) FROM table) AS 'num_records'
FROM
    table
LIMIT 3, 2