Skip to main content
Bumped by Community user
Bumped by Community user
Bumped by Community user
added 170 characters in body
Source Link

I have a large MySql InnoDB table with about 200 million rows. One of the columns has some data in it that I really don't need any more, so I'm thinking about removing that column, or setting all the cells in that column to NULL.

This column contains a text value of about 300 characters, so by removing it I'll save about 300 bytes x 200 million rows = 60GB.

But, will this leave a lot of "holes" in my data? Can MySql effectively and efficiently re-use this space without some sort of de-fragmentation? Or does that just not matter since it's InnoDB and on an SSD drive?

Thanks!

Update: The table has about columns total, but most of them are INT values. This text field is by far the biggest. Each row only takes up about 4KB of space total.

I have a large MySql InnoDB table with about 200 million rows. One of the columns has some data in it that I really don't need any more, so I'm thinking about removing that column, or setting all the cells in that column to NULL.

This column contains a text value of about 300 characters, so by removing it I'll save about 300 bytes x 200 million rows = 60GB.

But, will this leave a lot of "holes" in my data? Can MySql effectively and efficiently re-use this space without some sort of de-fragmentation? Or does that just not matter since it's InnoDB and on an SSD drive?

Thanks!

I have a large MySql InnoDB table with about 200 million rows. One of the columns has some data in it that I really don't need any more, so I'm thinking about removing that column, or setting all the cells in that column to NULL.

This column contains a text value of about 300 characters, so by removing it I'll save about 300 bytes x 200 million rows = 60GB.

But, will this leave a lot of "holes" in my data? Can MySql effectively and efficiently re-use this space without some sort of de-fragmentation? Or does that just not matter since it's InnoDB and on an SSD drive?

Thanks!

Update: The table has about columns total, but most of them are INT values. This text field is by far the biggest. Each row only takes up about 4KB of space total.

Source Link

InnoDB "holes" in large table 200 Million Rows?

I have a large MySql InnoDB table with about 200 million rows. One of the columns has some data in it that I really don't need any more, so I'm thinking about removing that column, or setting all the cells in that column to NULL.

This column contains a text value of about 300 characters, so by removing it I'll save about 300 bytes x 200 million rows = 60GB.

But, will this leave a lot of "holes" in my data? Can MySql effectively and efficiently re-use this space without some sort of de-fragmentation? Or does that just not matter since it's InnoDB and on an SSD drive?

Thanks!