Mysql - Calculate table size • Tuan Tran

Calculate table size

Whenever I add a new index to my table, I carefully consider the additional storage it will require.

To assess this, I typically run a command both before and after adding the index:

SELECT table_name AS `Table`,
       ROUND(data_length / 1024 / 1024, 2) AS `Data Size (MB)`,
       ROUND(index_length / 1024 / 1024, 2) AS `Index Size (MB)`
FROM information_schema.tables
WHERE table_schema = 'your_data_base_name'
  AND table_name = 'your_table_name';


+----------+----------------+-----------------+
| Table    | Data Size (MB) | Index Size (MB) |
+----------+----------------+-----------------+
| t_name   |       18922.00 |          571.36 |
+----------+----------------+-----------------+

Calculate entire database size

SELECT table_schema AS `Database`,
       ROUND(SUM(data_length) / 1024 / 1024, 2) AS `Total Data Size (MB)`,
       ROUND(SUM(index_length) / 1024 / 1024, 2) AS `Total Index Size (MB)`,
       ROUND((SUM(data_length) + SUM(index_length)) / 1024 / 1024, 2) AS `Total Size (MB)`
FROM information_schema.tables
WHERE table_schema = 'trill_sun_stg_2'
GROUP BY table_schema;

+-----------------+----------------------+-----------------------+-----------------+
| Database        | Total Data Size (MB) | Total Index Size (MB) | Total Size (MB) |
+-----------------+----------------------+-----------------------+-----------------+
| db_name         |             38236.00 |               3331.73 |        41567.73 |
+-----------------+----------------------+-----------------------+-----------------+
1 row in set (0.03 sec)