I’ve been researching how to optimize only fragmented tables in MySQL and reviewed this post on optimizing tables. It basically performs a query against the information_schema database for any table with
data_free > 0 and builds a SQL statement to
OPTIMIZE only those tables. I ran this query and it identified 148 tables for optimization. All of the tables identified are InnoDB tables. After executing the resultant optimization SQL script, I re-ran the original script to identify fragmented tables and it returned the exact same tables during the first pass.
I have seen conflicting posts regarding the InnoDB tables and the
OPTIMIZE command. Some say that
OPTIMIZE will not work with InnoDB tables and that you need to run
ALTER TABLE table_name ENGINE=INNODB. Others say that
OPTIMIZE actually calls the
ALTER TABLE command when executing against InnoDB tables. With that in mind, I ran the
ALTER TABLE command against one of the InnoDB tables identified as being fragmented (
data_free > 0) and found that the
data_free did not change afterwards. It’s still greater than 0. I also re-started MySQL and checked it only to find the same results.
Now, we have several servers running MySQL 5.5.29 in our organization and I ran a query against all of them to identify any InnoDB tables with
DATA_FREE=0 or NULL and none were returned. They are all greater than zero.
I also ran the
OPTIMIZE command against a few
MyISAM tables where
DATA_FREE was greater than zero and verify that it was zero afterwards.
Can anyone shed some light on this for me? What is the proper method to remove fragmentation from InnoDB tables? What is the proper method to determine fragmented InnoDB tables?