Counting all results before LIMIT

From: Moshe Jacobson <moshe(at)neadwerx(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Counting all results before LIMIT
Date: 2012-10-05 14:29:54
Message-ID: CAJ4CxLmpD5g=NGE=3zBKNO0ARgMCggKZNJgbqywctsOuEQrWNA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

We have a PHP web application that pulls results from the database and
paginates them.
We show e.g. "1-50 of 300" so the user knows how many total results there
are, and which ones are currently being displayed.
To achieve this, we use a query with LIMIT...OFFSET to get the displayed
results, and we do another identical query using count(*) to get the total
count.
Is there a more efficient way to do this that does not require us to do two
queries? I just feel that it's a waste of resources the way we do it.

Thanks!

--
Moshe Jacobson
Nead Werx, Inc. | Senior Systems Engineer
2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
moshe(at)neadwerx(dot)com | www.neadwerx.com

Responses

Browse pgsql-general by date

  From Date Subject
Next Message P Gouv 2012-10-05 14:52:00 Add a stemmer for fts
Previous Message Merlin Moncure 2012-10-05 13:55:18 Re: Re: What's faster? BEGIN ... EXCEPTION or CREATE TEMP TABLE IF NOT EXISTS?