Summary Tables with MySQL
December 15, 2006 6 Comments
At my new job I get to deal with GIS and eventually I’ll be putting their data on the web in one way or another. One of the recordsets I have dealt with already relates to households per voting district.
I was interested in seeing which district had the most constituent households. Ultimately, which representatives had the most constituents. I needed to pull a count of households per district—once I had those I could find out average households per district and other facts about the data based on the counts.
I hit a fork in the road…
*The Bad Way: *
Using select, on an ad hoc basis, scouring through large recordsets to find aggregate information. Example:
select field, count(field) as fieldcount from table group by field order by fieldcount asc
*The Good Way: *
Make a summary table.
create table fieldcounts select field, count(field) as fieldcount from table group by field order by fieldcount asc;
Sure, creating the table still takes some time—but now I can do my averages and other numerical analysis so much quicker and easier.
From there, I’ll just query my summary tables. Generate my charts/graphs from the summary table.
The only downside to this is data with high volatility – figure out the needs with the data and the frequency of reporting. If you only need to report on the data once a day, update the summary table once a day.