Re: Counting all results before LIMIT

Em 05/10/2012 12:17, Mike Christensen escreveu:
> You could use a windowing function. Something like:
>
> SELECT x, y, z, COUNT(*) OVER()
> FROM Foo
> LIMIT 50;

Good to know! I'll give a try!

>
> On Fri, Oct 5, 2012 at 8:02 AM, P Gouv <kadmos(at)gmail(dot)com
> <mailto: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.
>

I've used two queries for >100 000 (with filters applied - table has > 1
800 000 records), and is very acceptable (<200ms with 8Gb and Xeon dual
core).

Edson.
>
>
>
> On Fri, Oct 5, 2012 at 5:29 PM, Moshe Jacobson <moshe(at)neadwerx(dot)com
> <mailto: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 <mailto:moshe(at)neadwerx(dot)com> |
> www.neadwerx.com <http://www.neadwerx.com/>
>
>
>