In case anyone is interested, I have (sort of) solved this problem. Importantly, I learned that Firebird does not support PEAR DB functions like numRows(), so you can't do it that way (at least, my version of Firebird doesn't).
So what I have done is run the SELECT FIRST 15 SKIP x etc query - ie the actual data query - twice. The first time I just increment a counter variable until I get to the page limit+1 and then break out of the query. The second time I actually fetch the required data. Amazingly, this works *much* faster than issuing a SELECT COUNT query. Go figure. It still seems like a clunky solution, but it has helped somewhat. ----- Original Message ----- From: "Evan Morris" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, November 26, 2003 12:01 PM Subject: [PHP-DB] Optimizing query for public search engine > Hi all > > I'm using Firebird as the back-end for a web site. The search engine is > written in PHP, using bog-standard SQL queries. Here's the problem: > > In order to make the results listing work well, the page needs to know how > many records match the search criteria. This is so I can > > a) display '1 to 15 of 215 results' at the top of the page, and > b) have intelligent navigation at the bottom (no point having a 'Next' > button if there are no more records). > > The way I solved this problem originally was the have the search engine > build two SQL queries, one a SELECT COUNT query and one the actual SELECT > FIRST 15 SKIP x etc query. The SELECT COUNT query should run first > (obviously only if this is the first page of the result set), return the > size of the result set, and then the actual query should execute. > > Seems logical. And it works, after a fashion. However, the SELECT COUNT > query is ridiculously time-consuming. While I can get out the first 15 > records in a matter of microseconds, counting the total number of records > matching the search criteria can sometimes take more than two *minutes*, > depending on the structure of the query. > > So the question is, how do you resolve this issue? Am I missing something > obvious? Since almost every search results listing I have ever seen contains > this functionality, I assume the answer must be fairly simple and > well-known. Anyone care to pass it on? > > Thanks > > Evan Morris > [EMAIL PROTECTED] > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
