php - Best way to get result count before LIMIT was applied -

when paging through data comes db, need know how many pages there render page jump controls.

currently running query twice, once wrapped in count() determine total results, , second time limit applied results need current page.

this seems inefficient. there better way determine how many results have been returned before limit applied?

i using php , postgres.

pure sql

things have changed since 2008. can use window function full count , limited result in 1 query. (introduced postgresql 8.4 in 2009).

select foo       ,count(*) over() full_count   bar  <some condition> order  <some col> limit  <pagesize> offset <offset> 

note can considerably more expensive without total count. rows have counted, , shortcut taking top rows matching index not possible.
doesn't matter small tables, matters big tables.

consider sequence of events:

1. where clause (and join conditions, not here) filter qualifying rows base table(s).

   (group by , aggregate functions go here.)

2. window functions applied considering qualifying rows (depending on over clause , frame specification of function). simple count(*) over() based on rows.

3. order by

   (distinct or distinct on go here.)

4. limit / offset applied based on established order select rows return.

note limit / offset becomes increasingly inefficient growing number of rows in table. consider alternative approaches if need better performance:

• optimize query offset on large table

alternatives

there different approaches, too. postgres has internal bookkeeping how many rows affected last sql command. clients can access information or count rows (like psql).

for instance, can retrieve number of affected rows in plpgsql after executing sql command with:

get diagnostics integer_var = row_count; 

details in manual.

or can use pg_num_rows in php

• calculate number of rows affected batch query in postgresql

code examples:

• calculate number of rows affected batch query in postgresql