Deleting Entries from Table not reducing my DB file

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 space 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 whether 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.

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