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.

Databases use the file system to store the data.
As far as I know it is not possible to delete a record from a random access file.
So does that mean that when we do a DELETE FROM TABLE the size of the table i.e. the file that stores the table never decreases?
So databases essentially keep growing and never reduce in size?

share|improve this question
    
@RolandoMySQLDBA:Usually all InnoDB.Is there a difference on this between InnoDB and MyISAM? –  Jim Nov 10 '13 at 9:11

1 Answer 1

It is certainly possible to delete data from a random access file.

Realistically, though, virtually any database will do a soft delete and mark data as deleted rather than physically deleting data. At some point, something else will then reuse the space that the deleted row had been using (what operations reuse the space will depend on the database among other things). And different databases may provide tools to automatically or manually reclaim that space.

Practically, this is basically the same thing that the operating system does when you delete a file. The operating system removes the file system's pointer to the file just like the database removes the reference to the row from the index. The actual data for the file still exists on the drive. At some point, some other file system operation will eventually reuse that space. Some operating systems provide a "defrag" utility that will go through and make all the free space contiguous and move the allocated data to the "beginning" of the drive.

share|improve this answer
    
It is certainly possible to delete data from a random access file. I dissagree on this.AFAIK the only way to do this would be to either a)re-write the file to a backup file without the deleted entry and the drop the original file and rename the backup file to be in the place of the main file or b)somehow move all subsequent entries in the file to cover the deleted entries.So only the second approach really deletes data from a file but it is so time-consuming and error prone which is useless as a method –  Jim Nov 10 '13 at 9:15
    
Afaik, the only way to reclaim space in MySQL is rebuilding the table, either via OPTIMIZE TABLE OR ALTER TABLE. –  TheVedge Nov 10 '13 at 15:48
1  
@Jim - The fact that something is inefficient does not mean that it is impossible. It merely means that it is something that one would do as a periodic "housekeeping" process if it is actually necessary. –  Justin Cave Nov 11 '13 at 3:32

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.