I am trying to reduce the disk space usage of a table in an RDS instance of MySQL 5.6.23. It's an InnoDB table with about 8 million rows and 30 columns. Several of the columns are of type TEXT NULL DEFAULT NULL. One of the reasons why the table is so big is because rather than deleting rows from this table, they are instead marked as deleted via a flag column named 'deleted'.
After reading the MySQL documentation on storage requirements:
http://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html
It seems as though the storage required for a TEXT field depends on the length of text in the field rather than being a fixed size (L + 2 bytes, where L < 2^16 and where L is the length of the value in bytes). So although I've read elsewhere that these fields are in fact fixed width, I processed about 50,000 rows marked as deleted and set all their TEXT column values to null.
However, there was no reduction in disk space reported either by the MySQL client or the AWS Console RDS interface. Why didn't this free up disk space?
When you set the column value to NULL InnoDB would have to reorganize the record storage in order to reduce the total amount of disk space used by the table. You should see a reduction if you a dummy ALTER TABLE that is not dummy enough for MySQL to notice a short-circuit way to do it making it actually rebuild the table, or manually drop, re-create, and reinsert the records. OPTIMIZE TABLE should do it as well.