Mysql: setting int field as unsigned saves a ton of space. Why?

Question

I was under the impression that setting an int field to signed vs unsigned doesn't save any space. Today I set some columns to unsigned, as they don't ever store a negative value, and noticed a 15% reduction in table size. Some of my tables weren't reduced in size when I set their int columns to unsigned, some tables were substantially reduced in size. Anyone have an explanation for that?

Answer 1

@Frank Schmitt and @Kenny are correct - If you were watching only the file size before and after your data type change, you're seeing a different effect. Signed integers and unsigned integers occupy 4 bytes each, neither one is more space-efficient.

MySQL performs a table restructure when you add or change columns, and in some other circumstances. That is, it copies the contents of the table to a new file, and when it's done it drops the old file and renames the new file.

If the old file had lots of unused space because of past DELETEs, it would appear larger on disk than the data in it. You can get a more precise measure of the space used by data and indexes with:

mysql> SHOW TABLE STATUS FROM mydatabasename LIKE 'mytablename'\G

The data_length and index_length fields of that status show the space required. Another field data_free shows the amount of space in the datafile that is the fragmentation, which should (more or less) be reclaimed when you do an ALTER TABLE.