I have some large tables (hundreds of millions of rows) that are extremely active. They have heavy read and heavy modify activity. But, all modifications are only on recent records (data added within the last month is very volatile, but data earlier than that never changes).
We use the InnoDB engine for these tables because of the high concurrency (as well as the desire for transactions [potential rollback] on data changes and inserts). But, for all the data that's older than a month and never changes, having it in huge InnoDB tables is very cumbersome (constantly re-backing up all the old data that never changes, etc.). Plus, the data from the past six months is heavily accessed, but older data gets little use and is only necessary as historic information, so it would be nice to pack it away using MyISAM compressed tables. Ideally, what I'd like to do is create packed MyISAM tables of older data, possibly separated by quarter years, keep the last couple months in an InnoDB table, and use a single Merge table to access them. Obviously I can't do that because Merge tables only span MyISAM tables. My current plan is to put old data in packed MyISAM tables made accessible with a single Merge table, put the new data in an InnoDB table, and put my own frontend on it in my code to do UNIONs between the InnoDB and Merge tables as necessary. This is going to be a significant change in my code (plus a bunch of work to setup the job of transferring data from InnoDB to next MyISAM table at quarter-rollover time, etc.), so I'd like to know if anyone else has done something similar and has some suggestions from their experience. Or, if there is simply a better approach in general, then I'd love to hear your suggestions. Better yet, is there a plan to be able to create Merge tables spanning different engine types in the future? -keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
