Mysql – Are databases only growing in size

database-sizedatafilelinuxMySQLwindows

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?

Best Answer

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.

Related Solutions

Mysql – How to Prevent an Ever-Growing index file (MyISAM engine) From Causing Disk Fragmentation in Windows (NTFS partition)

When you perform any OS defragmentation or virus scan operations, please make sure mysql is shutdown. No utilities (mysql-based or otherwise) should be running against any table while the server is up. MySQL is not good to run on a PC that is not dedicated to MySQL DB only, especially in the presence of defragmentation or virus scan software running. Temp tables have also been known to get whacked by virus scan software.

At the very least, move the MyISAM table to a folder away from the standard datadir.

You should consider defragmenting the MyISAM table using:

OPTIMIZE TABLE calllog;

or you can do it in stages:

CREATE TABLE calllognew LIKE calllog;
ALTER TABLE calllognew DISABLE KEYS;
INSERT INTO calllognew SELECT * FROM calllog;
ALTER TABLE calllognew ENABLE KEYS;
ALTER TABLE calllog RENAME calllogold;
ALTER TABLE calllognew RENAME calllog;
DROP TABLE calllogold;

You may need to consider upgrading to MySQL 5.5 for the latest MyISAM storage engine code.

If you want to switch to InnoDB, you must configure it so that each table has its own tablespace.

Mysql – Deleting Entries from Table not reducing the DB file

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 space 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 whether 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.