Re: innodb ibd file size

[Ryan Thiessen]

Since you are using file-per table, you don't have to use the method that KimSeong suggested. Instead you can just use OPTIMIZE TABLE which recreates the table in place, as per the following example.

mysql> \! ls -lh;
total 117M
-rw-rw---- 1 mysql mysql 8.4K 2008-12-09 11:22 a.frm
-rw-rw---- 1 mysql mysql 116M 2008-12-09 11:23 a.ibd
mysql> truncate a;
Query OK, 0 rows affected (0.14 sec)

mysql> optimize table a;
+--------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+--------+----------+----------+-------------------------------------------------------------------+
| test.a | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| test.a | optimize | status | OK |
+--------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.21 sec)

mysql> \! ls -lh;
total 108K
-rw-rw---- 1 mysql mysql 8.4K 2008-12-09 11:23 a.frm
-rw-rw---- 1 mysql mysql 96K 2008-12-09 11:23 a.ibd