jbroder - 6:38 pm on Sep 12, 2011 (gmt 0)

I have a large MySQL MyISAM table that logs page views. The current size of the table is 7GB. It has been running for 18 months.

The page views log is the basis of licensing payments to a third party. We insert to the table all day long (for every page view), and then we query every quarter. After the end-of-year query in January, we don't need to look at last year's data unless we get audited.

Before we started this process, our db was a manageable size. Now it is really hard to work with mysqldump because the db is over twice the size it used to be. And of course, it just keeps getting bigger.

I am wondering if I should start archiving last year's data out of this table. I could do something like this:

CREATE table page_views_2010 as SELECT * FROM page_views WHERE year(mdate) = 2010

and then

DELETE from page_views WHERE year(mdate) = 2010


I wonder if that is what a well-informed DBA would do? Is it good practice? Or should I just live with the table getting larger and larger and larger? Any other advice?

thanks!

Jon