Re: Re: paginating : optimising queries

* Aman Patel <Aman.Patel@STJUDE.ORG>: > I've run in to this situation before. And it turns out that the extra > pre-"count" query is not worth it. So the steps become: > > 1) Do your main query and fetch the results. > 2) Use the count of results obtained in step 1. > 3) Calculate pagination numbers. > 4) Use array_slice to limit the results to a page window calculated in > step 3. I have to disagree here. When working with large tables, grabbing all results could cause some major issues (filling memory or exceeding PHP's memory cap, exceeding execution time, etc.). This sort of situation is exactly what the LIMIT clause was designed for. Try putting > 100,000 records in a table and doing the above; then try using a LIMIT statement to grab the subset you want. I personally pull the count and then store that in the session or cache it on the filesystem; that reduces the overhead of doing the count on each page. Then it becomes a matter of passing the offset from page to page. To the OP: look at PEAR's Pager class -- there are some great examples of how to integrate it with DB queries in the examples/ folder of that package. >> -----Original Message----- >> From: Zouari Fourat [mailto:fourat@gmail.com] >> Sent: Tuesday, March 22, 2005 10:03 AM >> To: php-db@lists.php.net >> Subject: [PHP-DB] Re: paginating : optimising queries >> >> >> no one can help me outta here ? >> >> >> On Mon, 21 Mar 2005 01:58:10 +0100, Zouari Fourat >> <fourat@gmail.com> wrote: >> > Hello, >> > i made my own paginating class, it permit me to paginate >> over selected >> > data and let me do good presentation (page 1, page 2 ...) i >> use adodb >> > for db related functions, my application should show in every page >> > this set of informations : >> > >> > Total results : Z, Total page : Y, You are on the page number X. >> > >> > To show thos informations, i must do a count on all the >> table, example >> > : the query built from the form submitted by the user is : >> > >> > select * from table where column=value and column like 'value%' >> > >> > to paginate that query, i'll add some LIMIT or LIMIT OFFSET >> for pgsql >> > (it's adodb frontend) so i'll get only the data for one >> page ! i use >> > this query : >> > >> > select count(*) from table >> > >> > with that global count i can calculate how much pages i have by >> > dividing count on the number of rows to show per page. >> > >> > with this method, the server will suffer for big tables... it's a >> > method that i've made without consulting what people do, can you do >> > this with only ONE query so i can optimise ? >> > >> > thank you for your help -- Matthew Weier O'Phinney | WEBSITES: Webmaster and IT Specialist | http://www.garden.org National Gardening Association | http://www.kidsgardening.com 802-863-5251 x156 | http://nationalgardenmonth.org mailto:matthew@garden.org | http://vermontbotanical.org