Re: Multiple counts on criteria - Approach to a problem

In response to Neil Saunders <n(dot)j(dot)saunders(at)gmail(dot)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/