Обсуждение: Multiple counts on criteria - Approach to a problem

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 (

Any suggestions gratefully received!

Ta,

Neil.

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.

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.
>
 > (snip)
>
> ...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 (
>
> Any suggestions gratefully received!
>

Here's the structure you want:

select
sum(case bedrooms when 1 then 1 else 0 end) as br1,
sum(case bedrooms when 2 then 1 else 0 end) as br2,
sum(case bedrooms when 3 then 1 else 0 end) as br3,
sum(case has_bbq when 1 then 1 else 0 end) as bbq,
sum(case has_pool when 1 then 1 else 0 end) as pool
from properties

in other words, you can put the criteria inside a case statement that
returns a 0 or 1, and use sum() over that case to count the rows that
returned a 1.

Adam

In response to Neil Saunders <n.j.saunders@gmail.com>:

> 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 (
>
> Any suggestions gratefully received!

In addition to the other excellent suggestions, you can provide estimates
at a very small cost by using explain:

EXPLAIN SELECT count(*) FROM properties WHERE bedrooms=3;

Then parse the explain output to get the estimated # of rows.  Very low
overhead, but of course it's only an estimate.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/