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


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

BUT When i delete rows using command


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

migrated from stackoverflow.com Jul 27 '12 at 12:51

This question came from our site for professional and enthusiast programmers.

  • 1
    According to this article, you could try mysqloptimize --all-databases – Andomar Jul 27 '12 at 12:47

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.

  • So do i need to bother about this thing? – bugwheels94 Jul 27 '12 at 12:55
  • If your application works in a manner which habitually deletes large quantities of data, you should write a cron to call an OPTIMIZE over the affected tables (sometime in the low traffic schedule). Otherwise no, not really. – Mihai Stancu Jul 27 '12 at 12:57
  • i should do it from phpmyadmin myself once a day or add this query to php file to run every time an user deletes almost 10 rows – bugwheels94 Jul 27 '12 at 13:00
  • Does your application insert anywhere near 10 rows in the same table during the same amount of time? If yes then it's not necessary. If no then it's only necessary once every few weeks (when the unclaimed space occupied by these leftovers becomes a few kilobytes). – Mihai Stancu Jul 27 '12 at 13:06

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.