Recently someone asked my if it's possible to find the total free
space within InnoDB. I thought this would be very easy as the
INFORMATION_SCHEMA.TABLES table has a DATA_FREE column. So we
could just use SELECT SUM(DATA_FREE) FROM
INFORMATION_SCHEMA.TABLES couldn't we?
 
So what does the DATA_FREE column tell us? It tells us the free
data within InnoDB for that particular table. A table can share a
tablespace with multiple other tables.
 
The tablespace which is used by a table depends on whether the
innodb_file_per_table was enabled during table creation and/or at
the last time the table was rebuild (e.g. by OPTIMIZE
TABLE).
 
If innodb_file_per_table was always disabled then this query
probably reports the correct free space:
SELECT DATA_FREE FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='InnoDB' LIMIT 1;
This is because all tables will share 1 …