How do I reduce MySQL's overhead as displayed in phpMyAdmin?

Question

In my MySQL database I see these statistics:

Type    Usage  
Data    16,384  Bytes  
Index   32,768  Bytes  
Overhead    405,0   MB  
Effective   -424,624,128    Bytes  
Total   49,152  Bytes  

When I try the commands check table, ALTER TABLE 'mytable' ENGINE = INNODB, OPTIMIZE TABLE, nothing happens to the overhead.

Why does nothing happen, should I worry, and when should I worry? I've seen other questions that state 'to worry when the overhead gets too large'. What is too large?

Answer 1

Overhead in PHPMyAdmin is calculated by 'Data_free' column returned by SHOW TABLE STATUS. It is actually explained in MySQL documentation: http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html

Data_free: The number of allocated but unused bytes.

Beginning with MySQL 5.1.24, this information is also shown for InnoDB tables (previously, it was in the Comment value). InnoDB tables report the free space of the tablespace to which the table belongs. For a table located in the shared tablespace, this is the free space of the shared tablespace. If you are using multiple tablespaces and the table has its own tablespace, the free space is for only that table. Free space means the number of completely free 1MB extents minus a safety margin. Even if free space displays as 0, it may be possible to insert rows as long as new extents need not be allocated.

However for InnoDB this is important "InnoDB tables report the free space of the tablespace to which the table belongs. For a table located in the shared tablespace, this is the free space of the shared tablespace.". So with a typical InnoDB setup ('innondb_file_per_table' is not set) you will get the free space for all tables and not for a single table. That is probably why phpMyAdmin ignores/discards the information or on Optimize table nothing happens.

You should read this post which clearly explains how to optimize a table in innodb.