Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
12-16-2012, 04:18 PM #1
Does truncate overwrite free space as well
Hi, i had someone ask me if truncate table overwrites the free space 256 and i honestly dont know, i looked at the doc for truncate but could not find anything specific. This might be apache setting not a mysql function attribute im thinking. I just dont know?
I know this next part is more of a apache question but i wanted to try to keep this topic together as much as i could. I did find this as a cron job just in case truncate does not wipe free space but i dont know how accurate this code is.
Last edited by durangod; 12-16-2012 at 05:29 PM.
12-17-2012, 08:15 AM #2
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.
Users who have thanked Old Pedant for this post:
12-17-2012, 03:26 PM #3
Thanks, yeah while it is good news that MyISAM does return the space back to the system almost immediatly i dont think thats gonna be good enough to satisfy this member.
I was hoping it would or i was hoping there was some kind of option to truncate that would truncate table and make it wipe the data at the same time, but since there is not i will need to look into like the above example and write some code to run a cron or run on damand.
I also found this but will also do some reading and verify the code does what the it says it does.
Thanks again Old Pedant