Hot to Get the Index and Data Size of a Table In MySQL/MariaDB

loading views

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

TableData Size (MB)Index Size (MB)Total Size (MB)
positions278382402451862
device_logs23126361426740
device_sessions14466243416900
battery_levels9389403513424
user_logs8712872