1

We are storing about 5 to 10 million events each year and now build a datamart that separates operational data out. My question:

  1. Would you move the logged events from a given year and store it in a data mart than delete the "event table" and start all over?
  2. Create a new table for each year where you store the events for a given year i.e. INSERT INTO 2011_actionrecord action = 'whathappened' etc. INSERT INTO 2012_actionrecord action = 'whathappened' etc.
  3. Or do something else?

Thanks much

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Rob
  • 11
  • 3

1 Answers1

2

1) Move the logged events from the transactional database to the datamart once it has 'expired'; this usually takes place over a 'rolling' period (so, you probably actually want to remove things monthly, or at a higher cycle). Add them to the datamart (including whatever are the necessary transformations), and delete the relevant records from the initial table. Don't bother deleting the table and indicies (although you may wish to manually refresh the indicies).

2) Use a partition, query (and insert to) the table as normal: MySQL Partitions

Clockwork-Muse
  • 12,806
  • 6
  • 31
  • 45