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

On 8/16/07, Rainer Bauer <usenet(at)munnin(dot)com> wrote:

> >> 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.

Categories are still finite state: you can simply store a count for
each category. Again it's just a case of knowing your data and
queries; it's not trying to solve a general infinite-possibilities
situation.

For instance, the OP mentioned wanting to get data on a particular
trader for the last week. Maintain a summary table that keeps counts
of each trader for each week, and ID bounds for the actual data table.
When you need to query the last 4 weeks, sum(). When you need to
query the last 30 days, sum() 4 weeks + a query on the master table
bounded by timestamp and ID range for the 5th week from the summary
table.

I'm sure there are sites out there that provide precise counts quickly
for extremely complex queries on gigantic datasets, but all the common
stuff is about specifics, not arbitrary queries. There are also
systems other than SQL RDBMS that can be used to drive such reporting.