0

I ran the following command on a mysql innodb database and was surprised to notice no change in disk usage, despite the fact that these columns contained around 150gb of data in longtext form.

update incidents set xml = NULL;

The database was previously storing very large raw xml files instead pointers to copies of the files stored on disk. No, that bad practice of storing many large files directly in a database isn't related to my question.

What I'd like to know is why the data seems to still occupy disk space after executing a command I'd expect to set all values in a column to null, thereby wiping the old data from the database. And yes, I know the drop command would probably be better suited to what I need, but at the moment I'm simply trying to understand why the above command didn't function the way I expected it to.

Hawkwing
  • 663
  • 1
  • 5
  • 13
  • This belongs on [dba.se] – Kermit Jul 16 '13 at 20:30
  • I suppose it is at that, Mike Brant. I'd assumed that mysql would release disk space by default, and my case was some outlier, or I might well have found that while searching. Also, FreshPrinceOfSO, I didn't know about dba.se. I'll direct such questions there in the future. – Hawkwing Jul 16 '13 at 20:46

1 Answers1

1

MySQL doesn't automatically release the space occupied in the fields in this situation. See http://forums.mysql.com/read.php?35,121880,121886 for more info.

mti2935
  • 11,465
  • 3
  • 29
  • 33