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