Hot to Get the Index and Data Size of a Table In MySQL/MariaDB
I created a couple of new indexes on a MySQL table with over 250 million rows yesterday. After I was done I got a warning about storage space. I wanted to know how much storage each table and their index require. It's easy to query for the requires storage space of each table with the following query.
Query
SELECT
TABLE_NAME AS `Table`,
ROUND(DATA_LENGTH / 1024 / 1024) AS `Data Size (MB)`,
ROUND(INDEX_LENGTH / 1024 / 1024) AS `Index Size (MB)`,
ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Total Size (MB)`
FROM
information_schema.TABLES
ORDER BY
(DATA_LENGTH + INDEX_LENGTH)
DESC;
Output
Table | Data Size (MB) | Index Size (MB) | Total Size (MB) |
---|---|---|---|
positions | 27838 | 24024 | 51862 |
device_logs | 23126 | 3614 | 26740 |
device_sessions | 14466 | 2434 | 16900 |
battery_levels | 9389 | 4035 | 13424 |
user_logs | 871 | 2 | 872 |