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?

  • @RolandoMySQLDBA:Usually all InnoDB.Is there a difference on this between InnoDB and MyISAM?
    – Jim
    Nov 10, 2013 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.

  • 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, 2013 at 9:15
  • Afaik, the only way to reclaim space in MySQL is rebuilding the table, either via OPTIMIZE TABLE OR ALTER TABLE.
    – ESG
    Nov 10, 2013 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. Nov 11, 2013 at 3:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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