How to reclaim disk space in InnoDB after dropping table

Question

I have a big InnoDB table in mysql. The problem is my disk space is almost full. I want to reduce my disk space. If I'll delete some rows from the table and use this command:

optimize TABLE_NAME

then my disk space will be reduced. But I want to drop the table. If I'll drop the table, there is not any table to optimize it! What is the appropriate command to reduce disk space after dropping the InnoDB table?

Answer 1

drop command will free disk space automatically.

Note: Assuming you are using innodb_file_per_table as you are able to free space by optimize table syntax.

Answer 2

You can try to configure your server to use innodb_file_per_table, but do make it sure that you have your backup and then drop and restore it. You can run the optimize table.

You can run to check if the innodb_file_per_table is ON

mysql> show variables like "innodb_file_per_table";
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)