Most solutions, I have seen, for the pagination problem query the DB twice. A first query to get the total number of rows and a second query to get the page with a LIMIT clause.

1- To get the total number of rows:
SELECT COUNT(*) as nb_rows FROM t;

2- To get the page number page_num:
SELECT field1, field2, ...., fieldn
FROM t
LIMIT page_num * itemsPerPage, itemsPerPage;

The purpose of the first query is to check if the page_num is between 0 and nb_row/itemsPerPage and to calculate the number of pages, to construct the navigation links.

This solution works fine, but my question is: is it better than to query the DB only once with a JOIN clause, although the num_rows will be attached to each row from the second query. I explain:

SELECT count(*) as nb_rows, t1.field1, t1.field2, ...., t1.fieldn
FROM t LEFT JOIN t as t1 ON 1=1
LIMIT page_num * itemsPerPage, itemsPerPage;

The above query will produce something like this (just an example):




nb_rowsidnameage
91name111
92name212
93name313
94name414


Yes, the nb_rows is attached to each row. Is it a memory problem? The function COUNT returns a BIGINT, it means 8 bytes. My query will return 8*itemsPerPage bytes extra, but query the DB once (less network traffic and may be less server execution time).

I hear you saying there is other problem, how to be sure, with one query; the page_num is in the permitted range? And the answer is if the query returns an empty result, it means that the page is outside the permitted range and that also means the user is trying to play with your application.

I'm not saying that the third query is better. I’m just asking a question.