Re: Multiple counts on criteria - Approach to a problem

Neil Saunders wrote:
> Hi all,
>
> I maintain an online property rental application. The main focus of
> the UI is the search engine, which I'd now like to improve by allowing
> filtering of the search results shown on some criteria, but provide a
> count of the number of properties that meet that criteria.
>
> For example, we're looking all properties, no criteria. I'd like to
> show something like:
>
> Bedrooms:
> 1 Bedroom (122)
> 2 Bedrooms (143)
> 3 Bedrooms (88)
>
> Facilities
> BBQ (232)
> Pool (122)
>
> ...and so on. My question is simple - What's the best way to implement
> this - Do I literally have to execute a count for the WHERE criteria
> with the filter criteria tagged on, or is there some clever trick that
> I'm not aware of? I'd rather not count in the application as I'd like
> to plan for the day we have up to 100k properties (

You could try using materialized views. Your churn is probably low
enough that it would make sense to update the views every time a
property is (de)listed.