I Have configured the mysql server with innodb per table. There was a table watchdog having approximate 19GB of watchdog.ibd file.

I have truncated the table now it has zero record. But the watchdog.ibd have same size.

What's the issue.? Does truncate does not work for removing the data from the .ibd file..?

I don't want to drop the table because in need that table.


Please keep in mind that TRUNCATE TABLE is a DDL command that tells the InnoDB storage engine to pretend there are no records.

According to the MySQL Documentation on TRUNCATE TABLE :

When fast truncation is used, it resets any AUTO_INCREMENT counter to zero. From MySQL 5.0.13 on, the AUTO_INCREMENT counter is reset to zero by TRUNCATE TABLE, regardless of whether there is a foreign key constraint.)

However, doing TRUNCATE TABLE does nothing for space already allocated.

To quickly drop the space down to zero rows, you can do the following:

# Algorithm 1
CREATE TABLE watchdog_new LIKE watchdog;
ALTER TABLE watchdog RENAME watchdog_zap;
ALTER TABLE watchdog_new RENAME watchdog;
DROP TABLE watchdog_zap;

Since TRUNCATE TABLE tells mysql that the table is considered empty, you can do this:

# Algorithm 2

These two steps behave the same way as Algorithm 1.

These steps also can be done with

# Algorithm 3

Internally, OPTIMIZE TABLE would perform Algorithm 1 and then perform ANALYZE TABLE. For InnoDB, the ANALYZE TABLE phase of OPTIMIZE TABLE is bypassed. In fact, it is unnecessary because index statistics are recomputed on each query executed against an InnoDB table.

  • 1
    a correction, index statistics are not updated after every query. They are updated after every 2 billion row operations, when 1/16th of the rows change, plus on certain metadata operations (ANALYZE TABLE, SHOW INDEXES FROM, SHOW TABLE STATUS) and most DDL operations. See mysqlperformanceblog.com/2011/10/06/… for more information . – Aaron Brown Jan 18 '12 at 16:29
  • My mistake. Index statistics for InnoDB are computed by approximation via dives into an index. This is quickly seen when running SHOW INDEX FROM command on an InnoDB table multiple times and watching the Cardinality column change with each successive run. – RolandoMySQLDBA Jan 18 '12 at 16:36
  • @AaronBrown your link is a good one since real statistics must be compiled under certain situations. – RolandoMySQLDBA Jan 18 '12 at 16:38
  • 1
    SHOW INDEX FROM is a metadata operation and actually causes the index statistics to recompute unless you have innodb_stats_on_meatadata=0. It's a case where observation changes the behavior. I recommend turning innodb_stats_on_metadata off in most cases. – Aaron Brown Jan 18 '12 at 19:06
  • 1
    For the first example, you can use RENAME TABLE which will move tables atomically: RENAME TABLE watchdog TO watchdog_zap, watchdog_new to watchdog – Romuald Brunet Feb 22 '12 at 11:32

Space isn't normally removed from the InnoDB files

To reclaim space, you have to OPTIMIZE TABLE which creates temporary tables/files and switches them. Or otherwise force this process via another operation using a temporary table/DROP+CREATE/CREATE TABLE..LIKE etc

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.