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.