How to store temp tables in memory during table "optimize"?

Question

When optimizing tables, SHOW PROCESSLIST lists it as "copying to tmp table". I'm trying to ensure the copy takes place in memory to speed up the process. I've tried adjusting the tmp_table_size to accommodate the 5G table. However, I've seen zero performance gain. I've changed the temp directory to tmpfs, but again, zero gain. In fact, regardless of the settings I change, the tmp table always seems to be written to temp files, on disk, within the database's directory.

I assume data is written to disk in a table rotation manner for safety, but I'm wondering if there is anyway to direct the optimize process to create the temp table in memory instead?

Thanks!

Answer 1

Please keep in mind OPTIMIZE TABLE is DDL not DML.

In terms of mechanism, this is what OPTIMIZE TABLE mydb.mytable; does under the hood

ALTER TABLE mydb.mytable ENGINE=MyISAM;
ANALYZE TABLE mydb.mytable;

or

CREATE TABLE mydb.mytable_new LIKE mydb.mytable;
ALTER TABLE mydb.mytable_new DISABLE KEYS;
INSERT INTO mydb.mytable_new SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytable_new ENABLE KEYS;
ALTER TABLE mydb.mytable RENAME mydb.mytable_old;
ALTER TABLE mydb.mytable_new RENAME mydb.mytable;

Since it's DDL, mysqld has no plausible reason to copy the table to memory.

BTW for InnoDB, you could do it manually if you want to skip ANALYZE TABLE mydb.mytable;

ALTER TABLE mydb.mytable ENGINE=InnoDB;

Even if you do ANALYZE TABLE mydb.mytable;, InnoDB will do deep dives into the index anyway to do cardinality approximations (See my post From where does the MySQL Query Optimizer read index statistics?).