MySQL Innodb: What is the difference between running 'mysqlcheck --optimize --all-database' compared to running 'optimize table' in the mysql client?

Question

Without having to script an 'optimize table' query to do all the databases/tables, we would like to run mysqlcheck --optimize --all-database instead.

What are the drawbacks of doing this through mysqlcheck; both would defragment the tables, correct?

Answer 1

mysqlcheck just runs a bunch of 'optimize table' SQL statements for you, so functionally they do the same thing.

Note that 'optimize table' for innodb just maps to an 'ALTER TABLE foo engine = innodb' statement. This rebuilds the entire table. Depending on the size of your table this may take a very long time, during which the table is locked and unavailable.