I have troubles interpreting the following stats from a
select (sum(data_length) + sum(INDEX_LENGTH))/ 1024 / 1024 as total,
sum(data_free)/ 1024 / 1024 as free ,
sum(data_length)/ 1024 / 1024 as data,
sum(INDEX_LENGTH/ 1024 / 1024) as index_data
from information_schema.tables t
where t.TABLE_SCHEMA = 'foo'
total | free | data | index_data
19.5469 20.0000 18.1875 1.3594
I assume you are using InnoDB, because it's the default storage engine in MySQL 5.5.
InnoDB tablespaces grow as you insert data, but the files do not shrink when you delete data. So for example if you insert 1 million rows, and then delete them, the file will have a lot of space that is physically allocated, but no longer used. InnoDB will re-use that space if it can before growing the tablespace file again.
Also, even if you don't delete, there can be some "wasted" space because when tablespace files are increased in size, they are expanded by a big chunk of pages, determined by the config option
innodb_autoextend_increment in megabytes. Until those pages are filled by data, they are free space.
Data_free reported by InnoDB is the amount of space "wasted" in empty pages in the central tablespace file. It has nothing to do with NULL values, it has to do with data pages that don't have rows in them.
Further, in MySQL 5.5, the default is for all tables to share one central tablespace called
ibdata. The data_Free for all tables in this tablespace will report the same figure, which is the amount of space in free pages in the whole tablespace, not just for one table.
You can also allocate a separate tablespace per table (
innodb_file_per_table=1), and for tables in separate tablespaces, you will see a different value per table for data_free.
Data_free does not include space left over by partially-filled pages, it only reports space left by totally empty pages. You'll notice that data_free is always a multiple of 16KB, which is the uniform page size in an InnoDB tablespace. Actually according to the documentation, the figure should be multiples of 1MB.