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

On Aug 15, 2007, at 9:36 AM, Phoenix Kiula wrote:

> I'm grappling with a lot of reporting code for our app that relies on
> queries such as:
>
> SELECT COUNT(*) FROM TABLE WHERE ....(conditions)...
>
> And I still do not find, from the discussions on this thread, any
> truly viable solution for this. The one suggestion is to have a
> separate counts table, which is fine for total aggregates related to,
> say, an ID. E.g., a table with:
>
> trader_id, trade_count
>
> But this is an overall count for the trader (in my example). What if I
> need a count of all his trades in the last one week. Then I need a
> timestamp condition in there as well. The number of such possibilities
> for multiple WHERE conditions is infinite...how should we account for
> all these avenues?

There is no general solution. While theoretically the multiple WHERE
conditions are infinite, in reality their limited to your actual use
cases and the solutions are thereby dictated by those. Using a
separate cache table is often a viable option used in situations
where constantly up to date realtime values. Another common option
is smart usage of indexes, i.e remember that you can index on the
results of a function applied to row values as well as partial
indexes. Another is table partitioning. Asking how to optimize
"SELECT COUNT(*) FROM TABLE WHER... (conditions)" is not a good
question as the solution is dependent on those conditions. Pick your
most common conditions and optimize for those.

Also, in many cases for reporting apps, 10 minutes is not long at
all. If you have reports that you can't make happen faster, schedule
and automate them.

Erik Jones

Software Developer | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com