Size of each table in a database of MySQL or MariaDB

I found something pretty useful, just a quick #MySQL / #MariaDB code snippet to show the size of each table of the currently selected database.

SELECT table_name ,
  round(((data_length + index_length) / 1024 / 1024), 2) as SIZE_MB
FROM information_schema.TABLES
WHERE table_schema = DATABASE() ORDER BY SIZE_MB DESC;

You have to start ‘mysql’ from CLI, in any terminal emulator of you choice, by adding the database name it should select when started, so lets say the database you’re looking to get the table sizes from is ‘wordpress’ you’d have to call ‘mysql wordpress’ and then in mysql you’d have to enter the whole code snippet from above and hit enter afterwards.

You’ll get a list of all tables ordered by their size, looking like this:

+---------------------------+---------+
| table_name                | SIZE_MB |
+---------------------------+---------+
| item                      | 1656.30 |
| item-body                 | 1166.86 |
| terms                     | 1029.72 |
| item-url                  |   11.89 |
| conv                      |    1.17 |
+---------------------------+---------+

Source