How to Check MySQL Database and Table Size Using information_schema

MySQL stores metadata about databases and tables in the information_schema database, specifically in the TABLES table.

The most relevant columns are:

TABLE_SCHEMA – database name

TABLE_NAME – table name

ENGINE – storage engine used

TABLE_ROWS – number of rows

DATA_LENGTH – size of the data

INDEX_LENGTH – size of the indexes

To query the total space used by a specific database:

SELECT CONCAT(ROUND(SUM(DATA_LENGTH/1024/1024),2),'MB') AS data FROM TABLES WHERE TABLE_SCHEMA='your_database_name';

To query the space used by a particular table within that database:

SELECT CONCAT(ROUND(SUM(DATA_LENGTH/1024/1024),2),'MB') AS data FROM TABLES WHERE TABLE_SCHEMA='your_database_name' AND TABLE_NAME='your_table_name';