Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

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
add comment

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
    
So do i need to bother about this thing? –  Ankit 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 –  Ankit 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
    
ok thanks for your time –  Ankit Jul 27 '12 at 13:06
add comment

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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