Most effective and efficient pagination style

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

Post Reply
someberry
Forum Contributor
Posts: 172
Joined: Mon Apr 11, 2005 5:16 am

Most effective and efficient pagination style

Post by someberry »

I'm going to be making a generic pagination class, but was wondering how would be best to pass (or process in the class) the amount of results that have been found.

Basically, do you have to perform 2 queries - one to get how many results are possible and one to LIMIT the results? Or is there a way to combine them into one query, perhaps using a JOIN or some such?

Thank you :)
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Most effective and efficient pagination style

Post by onion2k »

There's two options... do two queries, one to find out the total results and another to fetch the limited recordset. That method is more memory efficient but it does require 2 queries. The other method is to fetch the entire recordset and then seek (with mysql_data_seek() if you're using a vanilla mysql connection) to the right place to start. That uses more memory but it's a little faster in most circumstances because it's only a single query.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Most effective and efficient pagination style

Post by josh »

SQL_CALC_ROWS flag,

Also for displaying the actual page links theres already tons of classes that handle that for you
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Most effective and efficient pagination style

Post by Christopher »

Remember, you only need to do the COUNT(*) query once. After than you can pass it as a parameter or save it in the session.
(#10850)
User avatar
VirtuosiMedia
Forum Contributor
Posts: 133
Joined: Thu Jun 12, 2008 6:16 pm

Re: Most effective and efficient pagination style

Post by VirtuosiMedia »

I've been buried in JavaScript for about the last month, but one of the features I've used several times is the length function applied to an array to get the number of items in the array. PHP's equivalent is the sizeof($array) function. Might you be able to use that with a single query if the results array fetched is of the BOTH type? You could possibly eliminate COUNT altogether that way.
Last edited by VirtuosiMedia on Thu Sep 25, 2008 2:48 pm, edited 1 time in total.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Most effective and efficient pagination style

Post by Eran »

On separate count() versus SQL_CALC_FOUND_ROWS - http://www.mysqlperformanceblog.com/200 ... ound_rows/
On efficient pagination techniques - http://www.mysqlperformanceblog.com/200 ... -displays/

Cheers
Post Reply