Strategies on Pagination

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Re: Strategies on Pagination

Post by Chris Corbyn »

volomike wrote:Well, with COUNT(*), you have to run the query twice with every time they click Next, Next, Prev, Prev, Prev, etc.

You have to run it once to get the count of the total number of rows in the query.
You have to run it again to go get the offset page of that query.
I have no problem with that. It shouldn't be slow. Also, do a COUNT(primary_key_field) instead. If one more SELECT in my code is going to hurt my app then I've got some issues ;) Reads are cheap (usually).
User avatar
volomike
Forum Regular
Posts: 633
Joined: Wed Jan 16, 2008 9:04 am
Location: Myrtle Beach, South Carolina, USA

Re: Strategies on Pagination

Post by volomike »

I'm going to use crc32() on the SQL statement from the job search query, and store that in a cookie. If that value changes, then I need to redo the COUNT(jobs.id) all over again. That helps improve the speed on each pagination offset click.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Strategies on Pagination

Post by josh »

Wouldn't it more efficient and easier to just cache it server side, and not in a cookie?
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Re: Strategies on Pagination

Post by allspiritseve »

volomike wrote:I'm going to use crc32() on the SQL statement from the job search query, and store that in a cookie. If that value changes, then I need to redo the COUNT(jobs.id) all over again. That helps improve the speed on each pagination offset click.
Why are you going through all this trouble? Have you noticed slow COUNT queries with your existing dataset? It sounds to me like you're prematurely optimizing.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Re: Strategies on Pagination

Post by Chris Corbyn »

allspiritseve wrote:
volomike wrote:I'm going to use crc32() on the SQL statement from the job search query, and store that in a cookie. If that value changes, then I need to redo the COUNT(jobs.id) all over again. That helps improve the speed on each pagination offset click.
Why are you going through all this trouble? Have you noticed slow COUNT queries with your existing dataset? It sounds to me like you're prematurely optimizing.
+1

I've never had a need to stop running COUNT() queries. Databases are designed (and optimized) for this sort of thing.
wpsd2006
Forum Commoner
Posts: 66
Joined: Wed Jan 07, 2009 12:43 am

Re: Strategies on Pagination

Post by wpsd2006 »

i do some simple pagination
the row result only 15 or so it has image

and i create two function one is
when user click next it will call the query the data again
and the other one
i take all the data first and put it in array

the result using array twice faster than recall the query again
well i never try with 100 row of data but i think using array faster than recall the query again

i think using view is faster than join only a little bit
and second i think in mysql you can give index to a view
Post Reply