Re: Counting all results before LIMIT

You could use a windowing function. Something like:

SELECT x, y, z, COUNT(*) OVER()
FROM Foo
LIMIT 50;

On Fri, Oct 5, 2012 at 8:02 AM, P Gouv <kadmos(at)gmail(dot)com> wrote:

> You cant. There is an article about count performance. Generally its slow
> but latest version 9.2 i think supports index for count under some
> condition.But 300 isnt that much that you should worry.Another modern
> solution is to not count results just add one more at limit to see if there
> is next page.
>
>
> On Fri, Oct 5, 2012 at 5:29 PM, Moshe Jacobson <moshe(at)neadwerx(dot)com> wrote:
>
>> 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
>>
>>
>