The below query will be used to get the table sizes from a given database in MySQL.
The above query will produce an output like the below table
Query to get the list of tables with sizes
SELECT
TABLE_NAME AS 'Table',
ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS 'Size In MB'
FROM information_schema.TABLES
WHERE
TABLE_SCHEMA = "databasename"
ORDER BY
(DATA_LENGTH + INDEX_LENGTH) DESC
LIMIT 20
;
+----------------------------------+-----------+
| Table | Size (MB) |
+----------------------------------+-----------+
| emailqueue | 299242 |
| emaildetail | 203736 |
| templateitem | 25591 |
| templateoutput | 17006 |
+----------------------------------+-----------+
1 row in set (0.171 sec)Here I have taken only 20 rows to limit the query result else if the database is very large then it can impact on the performance, you can either remove the limit or change it as per your requirement.
