[ic] Rolling big tables (mysql)

[Kevin Walsh]

Grant <emailgrant at gmail.com> wrote:
> How do you guys go about periodically rolling tables that get too big?
>  I could export the table, mv the .txt file, create a new .txt file
> with the appropriate header, rm the .sql file, and restart IC.  Does
> anyone have a better method they like?  I'm using mysql.
> 
Well, it would depend upon the use to which you put your "big" tables,
although I have to say that your proposed deletion mechanism is far too
Heath Robinson for my taste. :-)

My suggestion would be to include a date column with each row, and use
that as a condition of your periodic delete.  I.e. delete all rows that
are over x months/years old.  That'll work if the table is just used as
a log, or something similar, but may be inconvenient if the table holds
orders, or other information you'd prefer to keep on hand.  If you want
to dump the (to be deleted) rows to a file beforehand then you can
easily do so.  Again, using the date column as your selection criteria.

You might want to save a summary of the (to be deleted) rows in
another table before deletion.  For instance, if you were deleting
orders that are over five (or whatever) years old then you could save
summary a row for every month.  The monthly row could summarise values
such as the number of orders, the total net/gross amounts, the average
net value of the orders and the tax revenue collected etc.  It would
probably take a long time for that table to be considered "big", and
you'd still have the requisite number of years worth of "real data" on
hand to keep the tax man happy.

>From your proposed solution, it seems as if you just want to clear out
the table and give yourself a blank space to start over with.  In that
case, I wonder why you're saving rows into a table in the first place -
especially as you'll lose all benefit of being able to select rows from
that table once the periodic clearout kicks in.  If row selection is not
an issue, then wouldn't it be better to just save the rows into a text
file in the first place, and then use something like logrotate to keep
the file size under control?

My view is usually that you either want the data or you don't.  If you
don't then either simply delete it when it gets old, or don't save it
in the first place.  If you do want to keep the data then either leave
it in the database or (if appropriate) delete it and keep a summary row
instead.

-- 
   _/   _/  _/_/_/_/  _/    _/  _/_/_/  _/    _/
  _/_/_/   _/_/      _/    _/    _/    _/_/  _/   K e v i n   W a l s h
 _/ _/    _/          _/ _/     _/    _/  _/_/    kevin at cursor.biz
_/   _/  _/_/_/_/      _/    _/_/_/  _/    _/