List:General Discussion« Previous MessageNext Message »
From:mos Date:July 14 2004 7:05pm
Subject:Re: deleting millions of records did not change the file size
View as plain text  
At 11:04 AM 7/14/2004, you wrote:
>My question in brief is why wouldn't the physical mysql files change in size
>if a substantial percentage of the records are deleted from a given table???

Why would it? If you delete 1 row from the middle of the table, it leaves a 
hole. This space is recovered when a new row is added. If MySQL changed the 
physical file size every time a row or a group of rows are deleted, it 
would slow down to a crawl. It would have to physically move all the rows 
up to fill the hole.

If you want to get rid of the holes and shrink the file size, use Optimize 
Table.

Manual:
OPTIMIZE TABLE should be used if you have deleted a large part of a table 
or if you have made many changes to a table with variable-length rows 
(tables that have VARCHAR, BLOB, or TEXT columns). Deleted records are 
maintained in a linked list and subsequent INSERT operations reuse old 
record positions. You can use OPTIMIZE TABLE to reclaim the unused space 
and to defragment the data file.

http://dev.mysql.com/doc/mysql/en/OPTIMIZE_TABLE.html

Mike

Thread
deleting millions of records did not change the file sizeGil Vidals14 Jul
  • Re: deleting millions of records did not change the file sizeDan Nelson14 Jul
  • Re: deleting millions of records did not change the file sizemos14 Jul
RE: deleting millions of records did not change the file sizeMarvin Wright14 Jul