Re: table size growth

[Rick James]

> The CPU are fine
There's an old saying where I come from: "If it ain't broke, don't fix it."

> shows 17 indexes
Possibly some of those are never used. Do you know about "compound" indexes? Do you understand that INDEX(a,b) can be more powerful than INDEX(a), INDEX(b)?

Use EXPLAIN SELECT ... to see which index a query is using. Usually it will use only one index.

Are the indexes HASH or BTree? I don't know the inner workings of this "hash", but some implementations double the memory footprint all at once at certain points in hash-table growth. BTrees inherently have the concept of a "block"; a block split adds exactly one (sometimes two) more block at seemingly random times during growth. MyISAM uses 1KB blocks, InnoDB uses 16KB, I don't know about MEMORY.

700MB+500MB -- Smells like a hash based on a power of 2 doubling itself.

Do you find that INDEX_LENGTH/1024 is always a whole number? Does it increase in uniform amounts?

Everything is a tradeoff. Memory is burned to avoid CPU. Memory allocation is incremented in chunks to make consistency and/or simplicity in the CPU work. Etc.

MEMORY has a drawback -- Datatypes are fixed length. VARCHAR(100) CHARSET utf8 consumes 302 bytes always. Do you have any VAR... fields? This is another tradeoff. In some cases, it leads users to say "MyISAM is actually better overall than MEMORY."