Query below returns tables in a database with space they use and space used by indexes ordered from the ones using most.
For InnoDB storage engine, column data_length represent size of clustered index which contains all data. Results for InnoDB from index_length and data_length are only approximation.
select table_schema as database_name, table_name, round(1.0*data_length/1024/1024, 2) as data_size, round(index_length/1024/1024, 2) as index_size, round((data_length + index_length)/1024/1024, 2) as total_size from information_schema.tables where table_schema not in('information_schema', 'mysql', 'sys', 'performance_schema') -- and table_schema = 'your database name' order by total_size desc;
- database_name - database (schema) name
- table_name - table name
- data_size - data size in megabytes
- index_size - size of table all indexes in megabytes
- total_size - total size, data and indexes
- One row represents one table
- Scope of rows: all tables in database with specific storage engine
- Ordered by total table size