Re: Insertion performance degrade with large index

[Rick James]

Yes, performance degrades. It must.

With MyISAM all indexes are cached in the key_buffer (of size key_buffer_size, which should usually be set to something like 20% of available RAM).

MyISAM indexes (other than FULLTEXT and SPATIAL) are in straightforward BTree structures (in the .MYI file), and the blocks are 1KB.

delay_key_write has an impact on this.

If an index is much larger than key_buffer_size, then _random_ index updates as you INSERT will require a read-modify-write for nearly all rows.

Building a table without the keys, then adding them _may_ build the indexes by "sorting" instead of using the key_buffer. This can be significantly faster.

InnoDB uses different algorithms. TokuDB does things radically differently, and can better sustain a high write rate. This is because of the games it plays with how it delays index builds.