Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I'm planning to add a couple of usage statistics to my web application. Some common things like: registered users, amount of comments/posts, etc. over time (last 24 hours / last week / last month / last year) which I want to display graphically to all admins.
I know that I can retrieve all information from database queries, but I have a bad feeling going over "old data" over and over again.

Example: Yesterday, I requested the amount of users who registered for an account the last year. Admin XYZ requests the same statistic today. A simple select-all-users-reg-date-last-year query would be redundant and cause much database traffic, right?

What is the best approach of calculating statistical data like this?

share|improve this question
2  
Step 1. Define more precisely what you're worried about. If you run such a query once each day, then the cost is miniscule. You can waste more time trying to optimize it than you would actually save. Step 2. Are you asking about "precomputing" your statistics? That is, incrementing some bunch of counters for each access? Updating a bunch of count rows might be more costly than a query. Step 3. Measure. Try both and compare. –  S.Lott Feb 19 '12 at 22:21

1 Answer 1

up vote 2 down vote accepted

You can get arbitrarily fancy with things like this, but it's probably not worth it. Unless you've observed that you're actually causing too much database traffic with the straightforward way, just do that and move on to something else. If you are, then for the query you've described, you could precompute each day's new users and then just sum one year worth of totals at query time. You can go further than that, even, but it's really not likely to be worthwhile.

share|improve this answer
    
Thanks for sharing your thoughts. –  ottel142 Feb 19 '12 at 23:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.