View Table Sizes in MySQL/ MariaDB Databases

MySQL and MariaDB present a bunch of queryable objects that give you all sorts of insights into what’s happening with the database. 

The size of data stored in tabels is one such thing that can be easily queried directly in SQL, providing you have SELECT access to the information_schema. 

Create the two below views in a schema of your choice:

CREATE  OR REPLACE VIEW schema_size AS
SELECT   table_schema
,        round(SUM(((data_length + index_length) / 1024 / 1024)), 2) table_size_mb
FROM     information_schema.tables  
WHERE table_schema IN ('dv', 'da', 'hue')  
GROUP BY table_schema
ORDER BY table_size_mb DESC;

CREATE  OR REPLACE VIEW table_size AS
SELECT   table_schema
,        table_name 
,        round(SUM(((data_length + index_length) / 1024 / 1024)), 2) table_size_mb
FROM     information_schema.tables  
WHERE table_schema IN ('dv', 'da', 'hue')  
GROUP BY table_schema
,        table_name 
ORDER BY table_size_mb DESC;

See schema_size and table_size for more info.

You can now query each view to get the size, in megabytes, of a whole schema or individual table respectivley. 

SELECT * 
FROM   schema_size;

SELECT * 
FROM   table_size;

• schema_size – displays the aggregated size of each schema in the database.
• table_size – lists all tables in the database. You can add a filter to the table_schema column to limit the output.