
Knowing how much disk space individual tables take up is important for DB maintenance and debugging, and it can be accomplished with a single query in MySQL.
First the query, then we'll explain parts of it:
1SELECT table_schema
2 , table_name
3 , concat(round((data_length + index_length) / (1024 * 1024 * 1024)), ' GB') AS total_size
4 , concat(round(data_length / (1024 * 1024 * 1024)), ' GB') AS table_size
5 , concat(round(index_length / (1024 * 1024 * 1024)), ' GB') AS index_size
6FROM information_schema.tables
7WHERE table_type = 'BASE TABLE'
8 AND table_schema NOT IN ('information_schema', 'performance_schema', 'mysql');
Column explanations:
total_sizeis the total disk space used by the table and its indexestable_sizeis the disk space used by the tableindex_sizeis the disk space used by the table's indexes
The information_schema.tables table catalogs information about tables and views.
By default, InnoDB table stats have a cache of 24 hours, but they updated with the ANALYZE TABLE ... statement.
See "Calculating Table Size in PostgreSQL" for the PostgreSQL version of this query.

Calculating Table Size in PostgreSQL
Nov 26, 2021 · 3 min read
Knowing how much disk space individual tables take up is important for DB maintenance and debugging, and it can be accomplished with a single query in PostgreSQL.
Finding the largest tables
It's fairly easy to modify the above query to order by the largest total size:
1SELECT table_schema
2 , table_name
3 , concat(round((data_length + index_length) / (1024 * 1024 * 1024)), ' GB') AS total_size
4 , concat(round(data_length / (1024 * 1024 * 1024)), ' GB') AS table_size
5 , concat(round(index_length / (1024 * 1024 * 1024)), ' GB') AS index_size
6FROM information_schema.tables
7WHERE table_type = 'BASE TABLE'
8 AND table_schema NOT IN ('information_schema', 'performance_schema', 'mysql')
9ORDER BY data_length + index_length DESC
10LIMIT 10;
This could be used to help debug a DB running out of space, or similar administrative tasks.
Finding the largest schemas
If you aggregate by table_schema you can find which schemas are the largest:
1SELECT table_schema
2 , concat(round(sum((data_length + index_length) / (1024 * 1024 * 1024))), ' GB') AS total_size
3 , concat(round(sum(data_length / (1024 * 1024 * 1024))), ' GB') AS table_size
4 , concat(round(sum(index_length / (1024 * 1024 * 1024))), ' GB') AS index_size
5FROM information_schema.tables
6WHERE table_type = 'BASE TABLE'
7 AND table_schema NOT IN ('information_schema', 'performance_schema', 'mysql')
8GROUP BY table_schema
9ORDER BY sum(data_length + index_length) DESC
10LIMIT 10;




