Re: [PHP] Re: Paginating searchs = performance problem
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. Ivo -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Paginating searchs = performance problem
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 YeeHaw And you're all set. it works regardless of the complexity of joins and other stuff. 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. | `' -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Paginating searchs = performance problem
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 Yep mysql specific. Postgres you have to do it the old fashioned way: $count_query = SELECT COUNT(...); $fetch_query = SELECT . -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] 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 YeeHaw 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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Paginating searchs = performance problem
On Mon, October 23, 2006 4:55 am, Ivo F.A.C. Fokkema wrote: 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... :) You also may not have the number defined as a constant in MySQL, so you have to know that it's, er, 2, I think... I remember having to do a define() in PHP and splicing it into the query on some box... Or maybe I was just being particularly stupid that day... -- Some people have a gift link here. Know what I want? I want you to buy a CD from some starving artist. http://cdbaby.com/browse/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] 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. | `' -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
