Check MySQL Database Size Using SQL Query

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

Reference:
https://www.suse.com/communities/conversations/check-mysql-database-size-using-sql-query/

Advertisements

One thought on “Check MySQL Database Size Using SQL Query

  1. you’re in point of fact a just right webmaster. The website loading pace is incredible. It sort of feels that you are doing any unique trick. Furthermore, The contents are masterwork. you have performed a excellent process in this matter!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s