How to Quickly Check MySQL Database, Table, and Index Size with SQL

To view the total space occupied by a MySQL database, run:

SELECT CONCAT(ROUND((SUM(DATA_LENGTH)+SUM(INDEX_LENGTH))/(1024*1024),2),'M') AS 'Database Size' FROM information_schema.TABLES WHERE TABLE_SCHEMA='your_database_name';

To view the total space used by all tables in a specific database, run:

SELECT CONCAT(ROUND(SUM(DATA_LENGTH)/(1024*1024),2),' MB') AS 'Total Table Size' FROM information_schema.TABLES WHERE TABLE_SCHEMA LIKE 'your_database_name';

To view the total space used by all indexes in a specific database, run:

SELECT CONCAT(ROUND(SUM(INDEX_LENGTH)/(1024*1024),2),' MB') AS 'Total Index Size' FROM information_schema.TABLES WHERE TABLE_SCHEMA LIKE 'your_database_name';

For detailed per‑table information—including table name, row count, data size, index size, and total size—run:

SELECT CONCAT(TABLE_SCHEMA,'.',TABLE_NAME) AS 'Table Name',
       TABLE_ROWS AS 'Number of Rows',
       CONCAT(ROUND(DATA_LENGTH/(1024*1024),3),'M') AS 'Data Size',
       CONCAT(ROUND(INDEX_LENGTH/(1024*1024),3),'M') AS 'Index Size',
       CONCAT(ROUND((DATA_LENGTH+INDEX_LENGTH)/(1024*1024),2),'M') AS 'Total'
FROM information_schema.TABLES
WHERE TABLE_SCHEMA LIKE 'your_database_name';