Is it possible to get total resultset count as part of paginated query response

Help
,
1

Our usecase requires paginated response & total resultset count. So is it possible to get both in a single response? Checked FOUND_ROWS() but it won’t work for a paginated query. So is there a way to achieve this?

1 Like
2

I think, it is possible. Maybe, you can use the LIMIT and OFFSET clauses in your SELECT statement to limit the number of rows returned and specify the starting point for the rows returned. You can then use the COUNT function to get the total number of rows in the resultset.

Here is an example of a SELECT statement that returns a paginated response and the total resultset count in a single response:

SELECT COUNT(*) as total_count, column1, column2, ...
FROM table_name
WHERE condition
LIMIT 10
OFFSET 20;

This SELECT statement will return 10 rows from the table, starting at row 21, and a column total_count that contains the total number of rows in the resultset.

You can then use the total_count column in your application to display the total number of rows in the resultset and allow the user to navigate through the pages of the resultset.

Let me know if that helps you.

1 Like
3

Thanks, @arnaud. :pray:

1 Like
4

Depending on how strict your SQL is, you might not be able to combine an aggregate function with non-grouped columns. If you experience that, you might want do COUNT(*) OVER () as total_count instead.

What I usually do is construct my queries to look sort of like this:

  select t.*,
         count(1) over () total_count
    from information_schema.tables t
   where t.table_type = 'Base Table'
order by table_name asc
   limit 10 offset 0;

Then, as arnaud says, you can use limit and offset for pagination.

1 Like
5

What?! I had no idea this was even possible, this can be huge

EDIT: Early tests show we could improve performance by 30% by doing 1 query instead of 2

2 Likes