MariaDB / MySQL Code Snippets


Index storage sorted by size

select database_name, table_name, index_name,
stat_value * @@innodb_page_size as size
from mysql.innodb_index_stats
where stat_name = 'size'
order by stat_value desc;

Find unallocated bytes in a table

See here: https://mariadb.com/kb/en/information-schema-tables-table/

select engine, 
table_schema,
table_name,
data_length, 
index_length, 
data_free
from information_schema.tables 
where data_free > 0
order by data_free