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?



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

ANALYZE TABLE mydb.mytable;


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?).

  • I thought running optimize on innodb tables was fine as it does the alter and analyze behind the scenes? - Hence the message - doesn't support optimize, doing stuff behind the scenes :) Is that not the case? – user2732180 Apr 19 '14 at 3:50
  • Yes, you are right. I updated my answer as to why you may want to skip ANALYZE TABLE on InnoDB sometimes. – RolandoMySQLDBA Apr 19 '14 at 4:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.