Check size of database and table in MySQL

Check size of database and table in MySQL

Check the size of database in MySQL

-- IN MB
SELECT table_schema AS "Database", SUM(data_length + index_length)/1024/1024 AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema

-- In GB
SELECT table_schema AS "Database", SUM(data_length + index_length ) /1024/1024/1024 AS "Size (GB)" FROM information_schema.TABLES GROUP BY table_schema

Check Size of tables in MySQL

--Exact size of tables in MB
SELECT table_schema "DB Name", table_name,(data_length + index_length)/1024/1024,1 "TableSizeMB" from information_schema.tables where table_schema='mysql' and table_name = 'test';

--Round size of tables in MB
SELECT table_schema "DB Name", table_name,round((data_length + index_length)/1024/1024,1) "TableSizeMB" from information_schema.tables where table_schema='mysql' and table_name = 'test';

--Size in GB
SELECT table_schema "DB Name", table_name,(data_length + index_length)/1024/1024/1024,1 "TableSizeGB" from information_schema.tables where table_schema='mysql' and table_name = 'test';

Note: I used the round function for make it more readable form, for exact size remove round of function from SQL queries.

Advertisements

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.