CODEWITHSUNDEEP
mysqlquery-optimizationinnodb
Slava Babin
Slava Babin

Reputation: 728

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.

Upvotes: 3

Views: 535

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562330

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.

Upvotes: 3

Related Questions