Re: Reduce Index space

[Rick James]

InnoDB has one giant pool of blocks, both index and data blocks. It is of size innodb_buffer_pool_size; blocks are 16K (assuming you aren't using the 'plugin'). Blocks are bumped on something like LRU basis. Writing of changed blocks is delayed in an attempt to avoid extra writes.

So, the blocks of an idle table will be purged as active tables take over.

To answer your implied question ("will the partitioning speed things up"), I doubt if it will help much. Remember that indexes are BTrees; even a terabyte table might have only 6 levels of BTree tree.

Ponder your access pattern, how big your indexes are, how big your data is, how BTrees work. Then give up on calculating the answer; instead, run a benchmark.