Page 2 of 2

Re: Strategies on Pagination

Posted: Tue Jan 13, 2009 9:00 pm
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).

Re: Strategies on Pagination

Posted: Tue Jan 13, 2009 9:20 pm
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.

Re: Strategies on Pagination

Posted: Tue Jan 13, 2009 9:23 pm
by josh
Wouldn't it more efficient and easier to just cache it server side, and not in a cookie?

Re: Strategies on Pagination

Posted: Tue Jan 13, 2009 9:35 pm
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.

Re: Strategies on Pagination

Posted: Tue Jan 13, 2009 10:47 pm
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.

Re: Strategies on Pagination

Posted: Wed Jan 14, 2009 1:02 am
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