Counting all results before LIMIT
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@neadwerx.com | www.neadwerx.com
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@neadwerx.com> wrote:
Show quoted text
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@neadwerx.com | www.neadwerx.com
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@gmail.com> wrote:
Show quoted text
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@neadwerx.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@neadwerx.com | www.neadwerx.com
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@gmail.com
<mailto:kadmos@gmail.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.
Show quoted text
On Fri, Oct 5, 2012 at 5:29 PM, Moshe Jacobson <moshe@neadwerx.com
<mailto:moshe@neadwerx.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@neadwerx.com <mailto:moshe@neadwerx.com> |
www.neadwerx.com <http://www.neadwerx.com/>