CodingForums.com - View Single Post - Does truncate overwrite free space as well

Old Pedant · 12-17-2012, 07:15 AM

You mean does it wipe the disk clean? No. Not at all.

I have done TRUNCATE TABLE on tables with several GIGABYTES of data, and the operation completes almost instantly. No possible way that anything had time to go wipe the disk clean.

Do you mean does TRUNCATE TABLE give the disk space back to the operating system immediately? That depends on the database type. With MyISAM, yes. Within milliseconds, it seems. You can go look at the actual disk space occupied by MySQL files and see it happen.

With INNODB, probably not. INNODB tables share disk files and while MySQL can release individual disk blocks, I wouldn't care to bet that it would always do so immediately.

12-17-2012, 07:15 AM	PM User \| #2
Old Pedant Supreme Master coder! Join Date: Feb 2009 Posts: 23,571 Thanks: 62 Thanked 4,061 Times in 4,030 Posts	You mean does it wipe the disk clean? No. Not at all. I have done TRUNCATE TABLE on tables with several GIGABYTES of data, and the operation completes almost instantly. No possible way that anything had time to go wipe the disk clean. Do you mean does TRUNCATE TABLE give the disk space back to the operating system immediately? That depends on the database type. With MyISAM, yes. Within milliseconds, it seems. You can go look at the actual disk space occupied by MySQL files and see it happen. With INNODB, probably not. INNODB tables share disk files and while MySQL can release individual disk blocks, I wouldn't care to bet that it would always do so immediately. __________________ An optimist sees the glass as half full. A pessimist sees the glass as half empty. A realist drinks it no matter how much there is.