How to get the sizes of the tables of a MySQL database?

You can use this query to show the size of a table (although you need to substitute the variables first):

SELECT 
    table_name AS `Table`, 
    round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
WHERE table_schema = "$DB_NAME"
    AND table_name = "$TABLE_NAME";

or this query to list the size of every table in every database, largest first:

SELECT 
     table_schema as `Database`, 
     table_name AS `Table`, 
     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
ORDER BY (data_length + index_length) DESC;

SELECT TABLE_NAME AS "Table Name", 
table_rows AS "Quant of Rows", ROUND( (
data_length + index_length
) /1024, 2 ) AS "Total Size Kb"
FROM information_schema.TABLES
WHERE information_schema.TABLES.table_schema = 'YOUR SCHEMA NAME/DATABASE NAME HERE'
LIMIT 0 , 30

You can get schema name from "information_schema" -> SCHEMATA table -> "SCHEMA_NAME" column

---

Additional You can get size of the mysql databases as following.

SELECT table_schema "DB Name", 
Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" 
FROM   information_schema.tables 
GROUP  BY table_schema
ORDER BY `DB Size in MB` DESC;

Result

DB Name              |      DB Size in MB

mydatabase_wrdp             39.1
information_schema          0.0

You can get additional details in here.

SELECT 
    table_name AS "Table",  
    round(((data_length + index_length) / 1024 / 1024), 2) as size   
FROM information_schema.TABLES  
WHERE table_schema = "YOUR_DATABASE_NAME"  
ORDER BY size DESC; 

This sorts the sizes (DB Size in MB).

If you want a query to use currently selected database. simply copy paste this query. (No modification required)

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;