0

I have a table named 'calls' with about 1,000,000 rows so far. Every day new calls are made, and I provide an aggregate daily stat, looking at todays calls only.

So a sum of all calls made today, grouped by their status.

My question is...

Is there any performance advantage gained to storing only todays calls in a seperate cache table that gets cleared daily. And then performing the aggregate just on this table.

Or does the fact that Im filtering the main table by date, kinda just ignore the other 990,000 lines. Meaning I wont gain any performance by creating a temp cache table to fill daily.

Kylie
  • 11,421
  • 11
  • 47
  • 78

1 Answers1

1

As always, it depends on the use case. You don't provide any information about how many records you generate per day...

I don't think premature optimisation is a good idea. If you're not experiencing any problems, why would you change? :)

Maybe a good first step would be to make sure the columns you're using to filter (maybe "date") are indexed (so everything runs faster).

Any modern database is more than capable to filter a table with one million records (in fact, it's nothing), so don't worry!

Magd Kudama
  • 3,229
  • 2
  • 21
  • 25
  • Ok, Im just wondering when it gets to 20,000,000 will it be an issue. Im generating about 30,000 rows a day. The dates are timestamps, so thats not really indexable is it? – Kylie Nov 03 '14 at 22:26
  • 20.000.000 is still nothing for a modern rdbms :). Seriously, don't worry. If you have a problem in the future, you can think about it at that time. Anyway, it's always good to make sure you have the right indexes in the right columns to avoid more scans than needed :). As suggested before, you should probably add an index to the "date" field. – Magd Kudama Nov 03 '14 at 22:30