Re: Yet Another COUNT(*)...WHERE...question

Decibel! wrote:

>On Thu, Aug 16, 2007 at 12:12:03PM +0200, Rainer Bauer wrote:
>> "Scott Marlowe" wrote:
>>
>> >When I go to amazon.com I only ever get three pages of results. ever.
>> > Because they know that returning 190 pages is not that useful, as
>> >hardly anyone is going to wander through that many pages.
>> >
>> >Google, you'll notice says "Results 1 - 10 of about 5,610,000 for
>> >blacksmith" i.e. it's guesstimating as well. no reason for google to
>> >look at every single row for blacksmith to know that there's about 5.6
>> >million.
>>
>> But if you go to eBay, they always give you an accurate count. Even if the no.
>> of items found is pretty large (example: <http://search.ebay.com/new>).
>
>And I'd bet money that they're using a full text search of some kind to
>get those results, which isn't remotely close to the same thing as a
>generic SELECT count(*).

Without text search (but with a category restriction):
<http://collectibles.listings.ebay.com/_W0QQsacatZ1QQsocmdZListingItemList>

I only wanted to show a counter-example for a big site which uses pagination
to display result sets and still reports accurate counts.

Anyway, what Phoenix is trying to say is that 2 queries are required: One to
get the total count and one to get the tuples for the current page. I reckon
it would help, if the query returning the result set could also report the
total no. of tuples found. Somthing like
SELECT COUNT(*), * FROM <table> WHERE <cond> OFFSET <o> LIMIT <l>

Or is there a way to do that?

Rainer