Re: Re: Paginating searchs = performance problem

On Mon, 23 Oct 2006 04:33:52 -0400, Robert Cummings wrote: > On Mon, 2006-10-23 at 10:13 +0200, Ivo F.A.C. Fokkema wrote: >> On Fri, 20 Oct 2006 12:06:26 -0400, Robert Cummings wrote: >> >> > 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). >> >> There must have been a reason why I started doing this... I used to use >> COUNT(*) first too, then run the full query but somehow this must have not >> worked for me when searching though a complex set of JOIN'ed tables or >> so... after which I have my query builder run the query first without >> the order clause. I'm going to look into this, see if I can track that >> down. >> >> But you're right, I should've mentioned that in his case a COUNT(*) >> could've been possible, since I didn't know his table structure or query. > > You can also use this dirty little sucker that's specific to MySQL > (AFAIK): > > SQL_CALC_FOUND_ROWS > > Just add it right after the SELECT keyword: > > SELECT SQL_CALC_FOUND_ROWS ... > > Then afterwards you issue another query: > > SELECT FOUND_ROWS() AS YeeeeeeeeeeHaaaaaaaaaw > > And you're all set. it works regardless of the complexity of joins and > other stuff. > > Cheers, > Rob. Supa-kewl! You da man! For my projects I use MySQL anyway, and this one even bypasses the LIMIT clause, according to the MySQL manual. All I have to do is check if my users are using MySQL >= 4.0... That's been released for a while, but you never know... :) Thanks, Ivo