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 evan@xxxxxxxxxxxxxxxxxx -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
