Deleting Entries from Table not reducing my DB file

Question

Iam usind INNODB Engine with innodb_table_per_file , so every table has it's own .ibd file instead of ibdata1 file.

When i Entered 512 Record in my users table its size get increased to 200kb from 96 kb but when I deleted all rows using Command

DELETE FROM USERS;

then my .ibd file didn't reduce not even by a single byte.

BUT When i delete rows using command

TRUNCATE TABLE USERS

Then my .ibd file got reduced to it's initial size which was on creation that is 96 kb.

Now i Want to know that is here any way to reduce .ibd files when i use DELETE FROM USERS WHERE ... command

Answer 1

On case:

TRUNCATE removes the file and creates another one. BTW in MySQL TRUNCATE is not allowed on tables with FK dependencies.

DELETE invalidates the record and the size the record used to occupy may be claimed later on for reuse.

One other operation forces the rearrangement of the data: OPTIMIZE.

Off case:

If your application works in a manner which habitually deletes large quantities of data, you should consider writing a cron to call an OPTIMIZE over the affected tables (sometime during low traffic).

Otherwise you should not worry about this because as stated here "Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions".

Take into consideration weather OPTIMIZE is transactional or not - DDL statements are not transactional in MySQL, not sure if OPTIMIZE is DDL.

If it's not you may need to do something else such as

• create a new table from the result of a full content select from the old table;
• wait for the data to be inserted into the new table;
• switch the table names with each other;
• drop the old table;

The new table will have a clean slate.

Answer 2

If you would like to mass compress all InnoDB tables please run the following:

MYSQL_USER=root
MYSQL_PASS=password
MYSQL_CONN="-u${MYSQL_USER} -p ${MYSQL_PASS}"
echo "SET SQL_LOG_BIN = 0;" > /root/ConvertMyISAMToInnoDB.sql
mysql ${MYSQL_CONN} -A --skip-column-names -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') InnoDBCompressionSQL FROM information_schema.tables WHERE engine='InnoDB' ORDER BY (data_length+index_length)" > /root/CompressInnoDB.sql
mysql ${MYSQL_CONN} -A < /root/CompressInnoDB.sql