Re: How to check table is successfully optimize?

[Rick James]

Why the concern about wasted space in a table? Sure, it matters; but not much.

SHOW TABLE STATUS LIKE 'tblname'\G
will give you "data free" -- this is the wasted space in the data.

For a MyISAM table, this will squeeze out all such space in the data:
OPTIMIZE TABLE tblname;

For an InnoDB table, this will squeeze out _some_ of the wasted space; you cannot get all of it out:
ALTER TABLE tblname ENGINE=InnoDB;

Almost any ALTER will squeeze most wasted space out of indexes.

I don't panic if 50% of the space is wasted -- not much performance is hurt. I don't bother with OPTIMIZE if less than 10% is wasted.