Run a query with a LIMIT/OFFSET and also get the t

Question

For pagination purposes, I need a run a query with the LIMIT and OFFSET clauses. But I also need a count of the number of rows that would be returned by that query without the LIMIT and OFFSET clauses.

I want to run:

SELECT * FROM table WHERE /* whatever */ ORDER BY col1 LIMIT ? OFFSET ?

And:

SELECT COUNT(*) FROM table WHERE /* whatever */

At the same time. Is there a way to do that, particularly a way that lets Postgres optimize it, so that it's faster than running both individually?

Answer 1

Yes. With a simple window function:

SELECT *, count(*) OVER() AS full_count
FROM   tbl
WHERE  /* whatever */
ORDER  BY col1
LIMIT  ?
OFFSET ?

Be aware that the cost will be substantially higher than without the total number, but still cheaper than two separate queries. Postgres has to actually count all rows in either case, which imposes a cost depending on the total number of qualifying rows. Details:

• Best way to get result count before LIMIT was applied

However, as Dani pointed out, when OFFSET is at least as great as the number of rows returned from the base query, no rows are returned. So we also don't get the full_count.

If that's not acceptable, a possible workaround that always returns the full count would be with a CTE and an OUTER JOIN:

WITH cte AS (
   SELECT *
   FROM   tbl
   WHERE  /* whatever */
   )
SELECT *
FROM  (
   TABLE  cte
   ORDER  BY col1
   LIMIT  ?
   OFFSET ?
   ) sub
RIGHT  JOIN (SELECT count(*) FROM cte) c(full_count) ON true;

You get a row of NULL values with the full_count appended if OFFSET is too big. Or it's appended to every row like in the first query.

If a row with all NULL values is a possible valid result you have to check offset >= full_count to disambiguate the origin of the empty row.

This still executes the base query only once. But it adds more overhead to the query and only pays if that's less than repeating the base query for the count.

If indexes supporting the final sort order are available, it might pay to include the ORDER BY in the CTE (redundantly).

Answer 2

Its bad practice to call two times same query for Just to get the total number of rows of the returend result. It will take execution time and will waste the server resource.

Better, you can use SQL_CALC_FOUND_ROWS in the query which will tell the MySQL to fetch the total number of row count along with the limit query results.

Example set as:

SELECT SQL_CALC_FOUND_ROWS employeeName, phoneNumber FROM employee WHERE employeeName LIKE 'a%' LIMIT 10;

SELECT FOUND_ROWS();

In the above Query, Just add SQL_CALC_FOUND_ROWS option in the rest required query and execute the second line i.e. SELECT FOUND_ROWS() returns the number of rows in the result set returned by that statement.

Answer 3

No.

There's perhaps some small gain you could theoretically gain over running them individually with enough complicated machinery under the hood. But, if you want to know how many rows match a condition you'll have to count them rather than just a LIMITed subset.