Re: Re: Paginating searchs = performance problem

On Fri, 2006-10-20 at 17:22 +0200, Ivo F.A.C. Fokkema wrote: > On Fri, 20 Oct 2006 17:04:35 +0200, Fourat Zouari wrote: > > > I have PHP/PostgreSQL application were i got a search page with some items > > to search, am building the search query on server side. > > > > I need to display a paginated search and for this i need to get the total > > count of lines matching the search before OFFSET/LIMITing my page, am i > > obliged to repeat the query twice ??? first to get the total count, second > > to get my page. > > > > it's very heavy > > > > Any one's suggesting better doing ? > > As far as I know, this is the only way. The first query, you don't need to > sort your data though, and you might be able to drop a join, depending on > whether or not you use the joined table in your WHERE clause. > > But I think due to caching the database will not take a long time for the > second query, since it just recently had (almost) the same query - YMMV. Hell no, don't use the same query twice. Use a count in the first query that only returns 1 row... the count. The second query can return the records (which may be less than the count returns since you're paging). Cheers, Rob. -- .------------------------------------------------------------. | InterJinn Application Framework - http://www.interjinn.com | :------------------------------------------------------------: | An application and templating framework for PHP. Boasting | | a powerful, scalable system for accessing system services | | such as forms, properties, sessions, and caches. InterJinn | | also provides an extremely flexible architecture for | | creating re-usable components quickly and easily. | `------------------------------------------------------------'