MySQL DELETE space release

Question

I've learned that execute DELETE sql in mysql will not release space, so i do an experiment about it.

During the experiment, i find when my data size is large(about 20000 rows), i delete half of the rows, the data length is not reduced. But when my data size is about 1000 rows, i try to delete half of the rows. Unexpectedly the data length will be reduced.

It's really confusing. I have search a lot about this problem, but got nothing. What's the reason?

My MySQL version is 5.6

Answer 1

InnoDB table statistics are not updated every time you DELETE. So the data_length, index_length, rows, average_row_length and data_free may not describe the precise state of the table at all times.

You might like to read:

• https://www.percona.com/blog/2011/10/06/when-does-innodb-update-table-statistics-and-when-it-can-bite/
• https://www.percona.com/blog/2017/09/11/updating-innodb-table-statistics-manually/

Even after the table stats are updated, they are not exact. They are estimates based on a limited sample of the table.

---

It should also be mentioned that the size of a tablespace file remains at its high-water mark, even if the data_length is reduced. The difference is space that is part of the file on your filesystem, but it is not occupied by data or indexes anymore. InnoDB should reuse that "free" space within the tablespace before it expands the file further.