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
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
> this functionality, I assume the answer must be fairly simple and
> well-known. Anyone care to pass it on?
> 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