Problem with InnoDB "per table" file sizes

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
#
TRUNCATE TABLE watchdog;
ALTER TABLE watchdog ENGINE=InnoDB;

These two steps behave the same way as Algorithm 1.

These steps also can be done with

#
# Algorithm 3
#
TRUNCATE TABLE watchdog;
OPTIMIZE TABLE watchdog;

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.

StackExchange Database Administrators Q#10779, answer score: 4