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/