What happens if you drop the database in InnoDB?
The positive side is that the .ibd file for the table is reduced after an optimize table. Just had the same problem myself. What happens is, that even if you drop the database, innodb will still not release disk space.
How to reduce table size in MySQL InnoDB?
Be sure to also retain a copy of your schema in your dump. Currently, you cannot remove a data file from the system tablespace. To decrease the system tablespace size, use this procedure: Use mysqldump to dump all your InnoDB tables. Stop the server. Remove all the existing tablespace files, including the ibdata and ib_log files.
What happens when you drop a table in MySQL?
Truncating or dropping a table stored in a shared tablespace creates free space within the shared tablespace data file, which can only be used for InnoDB data. In other words, a shared tablespace data file does not shrink in size after a table is truncated or dropped.
What to do if InnoDB does not release disk space?
If you don’t use innodb_file_per_table from the beginning, dumping all data, delete all file, recreate database and import data again is only way ( check answers of FlipMcF above ) If you can delete all data truncate command will delete data and reclaim diskspace for you. Alter table command will drop and recreate table so it can reclaim diskspace.
What happens when you use truncate table in InnoDB?
The biggest thing most people forget about TRUNCATE TABLE is that TRUNCATE TABLE is DDL and not DML. In InnoDB, the metadata within ibdata1 contains a numbered list of InnoDB tables. Using TRUNCATE TABLE causes the internal metadata id of the InnoDB table to shift. This happens because TRUNCATE TABLE effectively does the following:
Why does MySQL not release disk space after deleting data?
What happens is, that even if you drop the database, innodb will still not release disk space. I had to export, stop mysql, remove the files manually, start mysql, create database and users, and then import. Thank god I only had 200MB worth of rows, but it spared 250GB of innodb file. Fail by design.