Making pagination work with its count

Question

I have a question about how to have the pagination work in server side having mysql as database. I have figured most of it out and it works perfectly fine but i was thinking is there any way to have the count query eliminated. Let's say user searches for the word jack and the results count are 25 paginated by 10 per page. my paginator needs to know the amount of all rows to show correct amount in paginator. So the way i am doing it now is first i use a sql query like this to have the count of all the rows which correspond to that criteria:

"SELECT COUNT(id) AS count FROM " + table + query

Then i do another query against database like this that uses LIMIT and OFFSET options to have that exact page:

"SELECT * FROM " + table + query + " LIMIT ? OFFSET ?"

Then i return two objects to client. first the count of all rows and the seconds the rows user needs to see now. My question is is this the most efficient way to do this or is there any better way to do it too?

There is no (better) way to work out pagination without the count of the rows. — George, Jul 09 '18 at 10:48
Possible duplicate of [MySQL pagination without double-querying?](https://stackoverflow.com/questions/818567/mysql-pagination-without-double-querying) — Vignesh Raja, Jul 09 '18 at 11:52

score 0 · Answer 1 · answered Jul 09 '18 at 10:59

I didn't see anything wrong with your approach (although you can send the query to database in one trip). With the traditional way of pagination in database, you must know the total records, so it's just how to get the count.

improvements are mostly to do it in a different way.

Improvement 1: infinite scroll, this is get ride of pagination. May not be what you wanted, but we are seeing more and more website adopting this way. Does the user really need to know how many pages for a free text search?

Improvement 2: use ElasticSearch, instead of database. It's built for free text search and will definitely perform better than database. You can also get count (hits) and pages in one search request.

Thanks, Good points to have in mind for future and would be useful in some situations. but as this was a datatable kind of situation infinite scroll wouldn't be a good idea and it's a commercial application with too many tables so elasticsearch as database or in sync with database is not a good either. — , Jul 10 '18 at 08:50

score 0 · Accepted Answer · answered Jul 09 '18 at 12:19

You can achieve this with one query, but it will have burden on outputted data i.e. if you limit 1000 records for example, then total_records will show the number 1000 times with all rows in the result set. But at the same time, it will reduce 1 query:

SELECT 
  column1,
  column2,
  column3,
  (SELECT COUNT(id) FROM `table`) AS total_records 
FROM
  `table` 
LIMIT 0, 10

Making pagination work with its count

2 Answers2