Find Total Number of Results in MySQL Query With Offset+Limit

Find total number of results in mySQL query with offset+limit

Take a look at SQL_CALC_FOUND_ROWS

Run a query with a LIMIT/OFFSET and also get the total number of rows

Yes. With a simple window function:

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

Be aware that the cost will be substantially higher than without the total number, but typically still cheaper than two separate queries. Postgres has to actually count all rows either way, 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 full_count.

If that's not acceptable, a possible workaround to always return 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 one row of NULL values with the full_count appended if OFFSET is too big. Else, 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).

How to get the number of total results when there is LIMIT in query?

Add a column, total, for example:

select t.*
     , (select count(*) from tbl where col = t.col) as total
from tbl t
where t.col = 'anything'
limit 5

As stated by @Tim Biegeleisen: limit keyword is applied after everything else, so the count(*) still returns the right answer.

Is this possible to get total number of rows count with offset limit

You can use SQL_CALC_FOUND_ROWS like this

SELECT SQL_CALC_FOUND_ROWS * FROM users limit 0,5;

It gets the row count before applying any LIMIT clause. It does need another query to fetch the results but that query can simply be

SELECT FOUND_ROWS()

and hence you don't have to repeat your complicated query.

Get count query results with ignoring the LIMIT statement

MySQL supports a FOUND_ROWS() function to find the unlimited number of rows that would have been returned from the previous limited query.

SELECT SQL_CALC_FOUND_ROWS * FROM blah WHERE disabled = '0'  LIMIT 10,20
SELECT FOUND_ROWS();

Note that (a) you need to include the SQL_CALC_FOUND_ROWS option, and (b) that this is a specific MySQL extension that won't work on another RDBMS (though they each may have their own way of doing this.)

This isn't necessarily the best way of doing things, even if it might feel like it; you still have to issue two statements, you're introducing non-standard SQL, and the actual COUNTing is likely to be a similar speed to a simple SELECT COUNT(*)... anyway. I'd be inclined to stick to the standard way of doing it, myself.

Selecting all records using SQL LIMIT and OFFSET query

From the MySQL documentation:

To retrieve all rows from a certain offset up to the end of the result
set, you can use some large number for the second parameter. This
statement retrieves all rows from the 96th row to the last:

SELECT * FROM tbl LIMIT 95,18446744073709551615;

So getting all rows might look as follows:

SELECT * FROM tbl LIMIT 0,18446744073709551615;

OFFSET x LIMIT y messed up order of rows

This is caused because I am ordering on purchase_date. But there are multiple rows who have exactly the same purchase_date. MySQL does not guarantee a consistent order over multiple queries between rows who have the same amount of votes in an order-clause.

Adding another unique column in the order-clause solves the issue.

select  voucher.id, sale.purchase_date
    from  voucher
    inner join  sale  ON sale.id=voucher.sale
    order by  sale.purchase_date desc, voucher.id desc
    limit  x offset y;

---

Related Topics