Let's just say that InnoDB is not suited for archival purposes although you may choose to do so. You have a Clustered Index which bloats the data. Archival tables in InnoDB do not benefit from it since MVCC and ACID compliance are rendered useless. Data Pages and Index Pages are loaded into the InnoDB Buffer Pool. Accessing archival data from InnoDB would potentially push working set data out of the Buffer Pool as often as as you access it.
Think of these alternatives
If the archival data is read often via indexed queries, you could put such data into a table that uses the MyISAM Storage Engine. You can then do one of the following things to the MyISAM table:
- compressed to save space
- change ROW_FORMAT=Fixed to increase read speed
You could also configure key_buffer_size for the indexes of the MyISAM tables. Data pages for MyISAM are never cached, thus saving on RAM. You could then reduce the size of the InnoDB Buffer Pool in favor of the actual working set you need on hand.
If the archival data is read in bulk via SELECTs without indexes, you could stored the data in tables that use the ARCHIVE Storage Engine.
Please do not perform any JOINs between heterogeneous storage engines. In other words, do not join InnoDB to MyISAM for any reason.