Originally Posted by Ruben
I understand what this does but in what scenario would you use this option.
Meaning site is slow have a lot of valid visitors or what?

Why is it using memory to begin with?

In 2007 with version 7.1.0, those TEMPORARY high-transaction tables were moved from disk storage to memory storage. The idea behind this was probably due to the tables being temporary, and that they had a high volume of read/writes. Memory generally is faster than physical hard disk storage, especially in 2007 when the change was made.

But it came with a major trade-off. One that I've been seeing lately. The size of tables in memory is a setting that is rarely adjusted on standard hosting servers or set to a ceiling on shared server environment. IIRC, the size is 16MB.

The ONLINE and TOPIC_VIEWS tables use VARCHAR (variable-length) fields.

When the table is in MEMORY, those VARCHAR fields are no longer variable-length. They are converted to static CHAR field max length. Due to this, a rather small table needs a lot of memory when its storage engine is set to MEMORY. Effectively, VARCHAR becomes CHAR with the MEMORY engine.

"MEMORY tables use a fixed-length row-storage format. Variable-length types such as VARCHAR are stored using a fixed length."
SOURCE: https://dev.mysql.com/doc/refman/8....-engine-characteristics-of-memory-tables


Example:
Both of the items in this screenshot are the same table.

[Linked Image]


When you hit about 5,000 rows on the static field size ONLINE table (using memory/heap storage), you will be very close to that that 16MB default maximum, and an error will be generated if attempting to store more. The same error that Morgan is having.

Moving the table to disk does not have the 16MB limitation, and the fields will only use as much as they need, rather than their maximum character width. But disk storage is generally slower than memory storage -- especially for a high-transaction table.
Attachments