Search code examples
mysqlquery-optimizationinnodb

Does it make sense to optimize the table after alter table drop column?

I dropped the column name in table employees. If I run OPTIMIZE TABLE employees, will it reduce space usage?

My thoughts:

The documentation says that optimize table is equal to alter table for InnoDB (if I read this https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html#optimize-table-innodb-details correctly).

Also, alter table drop column changes rows structure in the table, so it should rewrite all rows. This is where, I assume, optimization happens.

Solution

  • It's not necessary to OPTIMIZE TABLE on an InnoDB table after running any ALTER TABLE that changes the row size.

    The ALTER TABLE copies rows into a new tablespace, and rebuilds indexes. This will accomplish the same defragmentation you hoped to do with OPTIMIZE TABLE.