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 issuesvolomike 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.
Strategies on Pagination
Moderator: General Moderators
- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
Re: Strategies on Pagination
- volomike
- Forum Regular
- Posts: 633
- Joined: Wed Jan 16, 2008 9:04 am
- Location: Myrtle Beach, South Carolina, USA
Re: Strategies on Pagination
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
Wouldn't it more efficient and easier to just cache it server side, and not in a cookie?
- allspiritseve
- DevNet Resident
- Posts: 1174
- Joined: Thu Mar 06, 2008 8:23 am
- Location: Ann Arbor, MI (USA)
Re: Strategies on Pagination
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.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.
- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
Re: Strategies on Pagination
+1allspiritseve wrote: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.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.
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
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
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