HomeJavaHow To Find the MySql Database Size

How To Find the MySql Database Size

MySQL like most other relational databases provides us with useful metadata about the database. The official MySQL documentation refers to the INFORMATION_SCHEMA metadata as tables.

We can get all the information from INFORMATION_SCHEMA tables, like views, user_privilieges, columns, and tables. For our purposes, we will focus on the tables metadata, which we can query to actually extract the size of various tables in the system.

SQL Script

Script #1: This script lists out the entires databases sizes.

SELECT table_schema AS "Database Name", 
       Round(Sum(data_length + index_length) / 1024 / 1024 / 1024, 1) AS "Size (GB)" 
FROM information_schema.tables 
GROUP BY table_schema

Script #2: This script list out the size of a particular database.

SELECT table_schema AS "Database Name", 
       Round(Sum(data_length + index_length) / 1024 / 1024 / 1024, 1) AS "Size (GB)" 
FROM information_schema.tables 
WHERE table_schema = "<database name>"

Script #3: Now if you want to check the size of the individual tables then the script is for you.

SELECT TABLE_NAME AS `Table`, ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS "Size (MB)"
FROM information_schema.tables
WHERE TABLE_SCHEMA = "<database name>"
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular