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!
Best Answer
Please keep in mind
OPTIMIZE TABLE
is DDL not DML.In terms of mechanism, this is what
OPTIMIZE TABLE mydb.mytable;
does under the hoodor
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;
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?).