1

I have a log table that contains a large number of user transactions (logs). I am trying to create a webpage that displays statistics (count, average, and some complex calculations...) of the user transactions, but want to fetch from a Statistics table instead of querying the original transaction table because of the performance concern. One possible way might be updating the Statistics table whenever a row is inserted. And, another way can be updating the Statistics table periodically.

Both options sound inefficient, so I am wondering there is any particular method to achieve it in common database systems?

MaxHeap
  • 1,138
  • 2
  • 11
  • 20
  • 2
    in mysql I would create an event. You choose the interval it fires off. Maintain a metrics table for key metrics. Your queries will fly. You determine the staleness factor based on the interval of the refresh. See [this write-up for events](http://stackoverflow.com/a/32508935) – Drew Nov 25 '15 at 21:09

1 Answers1

1

If you don't need statistics in real time (if near real time is ok for you, usually it is for most people), one thing that reports that need some complex calculations usually do is to geneate these reports in a periodic manner (let's say every X minutes, depends on how big is your data of course).

This way your users can access static data, which is pretty much easy to serve, and you won't push too much load into your analytics server.

Leo
  • 751
  • 4
  • 29