I’m writing for advice on the most efficent practices to archive data into smaller, horizontally partitioned tables.
I have a logging type table (‘logs’ for lack of a better name) which is currently taking 200K records on a daily basis. The table is growing fast enough where I would like to horizontally partition the table into smaller, date-centric tables (i.e. logs_YYYYMM). There would be a primary ‘logs’ table that would take the production inserts, and would be the staging area for migrating log data into the currently applicable archive table (not to be confused with ARCHIVE storage engine).
The time interval is still up in the air, but my initial plan is to run an hourly archiving process to move data from ‘logs’ into the proper archive table. All tables will be using MyISAM storage engine. I was planning on not having an auto_incrementing field in the ‘logs’ table as the records are just going to be cleaned out as they get archived. However, the archived tables would have an id type auto incrementing primary key. Is this a bad decision to not have an id field on the main ‘logs’ table?
My primary question is what the most efficient way to perform this type of data migration with the least impact on the production logging into the ‘logs’ table in a MySQL 5.0 environment?
Any advice would be greatly appreciated.