Check MySQL Database Size Using SQL Query
Don’t have access to the command prompt? Here’s how you can find the database size in MySQL. Become a power MySQL user without access to the power of command line.
Execute the following SQL Queries as per your need:
Get Size of all database tables
SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table', CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2)," Mb") AS Size FROM INFORMATION_SCHEMA.TABLES;
Replace YOUR_DB_NAME with your database name in following 2 examples:
Get Size of specific database tables
SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table', CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2)," MB") AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA like '%YOUR_DB_NAME%' ;
Get size of entire DB
SELECT CONCAT(sum(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2))," MB") AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA like '%YOUR_DB_NAME%' ;
Notes:
CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2)," Mb")
Used to convert to Mb and concat Mb to output making it more meaningful
DATA_LENGTH + INDEX_LENGTH - DATA_FREE
Gives actual size in bytes
That’s all for now. I will be back with more MySQL tips soon.
Comments
That’s very wonderful, I’ve noticed on loaded servers it’s very slow to execute. I also noted there’s a discrepancy between this number and the numbers the OS sees particularly using `du -sh`
—
Joel