Check MySQL Database size using SQL Query

Check MySQL Database size using SQL Query

In case you don’t have SSH access to the server and you want to know the size of any database and/or database tables, you can use SQL to accomplish this.

This gives the ability to the MySQL admins to check MySQL database size using SQL query.

Check MySQL Database size using SQL Query – Specific database only

In order to check the size of a specific MySQL database, you can use the following query.

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%’ ;

Where, you will have to change YOUR_DB_NAME to the database name you want to check the size for.

Check MySQL Database size using SQL Query – All databases only (no table details)

To get the database size for all the databases on a MySQL server you can use the following query.

SELECT
CONCAT(sum(ROUND(((DATA_LENGTH + INDEX_LENGTH – DATA_FREE) / 1024 / 1024),2)),” MB”) AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA like ‘%’ ;

Check MySQL Database size using SQL Query – Database tables

To get the size of all the tables within a database, you can use the following query.

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%’ ;

Where, you will have to change YOUR_DB_NAME to the database name you want to check the size for.

Check MySQL Database size using SQL Query – All Database tables

To display the size of all the tables under all the database hosted on the server, use the following SQL.

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;

Important

While calculating the size of MySQL database table, the following gives you the actual size of the database/table in bytes:

DATA_LENGTH + INDEX_LENGTH - DATA_FREE

And, the following part in the SQL converts the bytes into Mb to make it more human readble.

CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2)," Mb")

Check MySQL Database size using SQL Query

No Comments

Post a Comment

Time limit is exhausted. Please reload CAPTCHA.