As a Database Administrator(DBA), your job want you to know the most atomic details of databases in your server. It happens for me many times, my boss/ delivery manager asking me, what is the size of a specific database or specific table, in this kind of situation, producing the right data will help make right decision. From my experience, I understood, it is always better to say, I will give you data in few minutes, instead of producing the incorrect data, which I had been doing for a long time.
This post is about identifying the size of a database(s) or table(s). The simple script, I have been using it for quite a long, if not wrong when I started my career as DBA. You could have probably seen this/similar script on other forums as well and there are many other methods too. I am reproducing this handy script here to get work done.
select table_name as "Table Name", sum(data_length+index_length)/1024/1024 as "Table Size in MB" from information_schema.tables where table_schema = 'Database Name' and table_name = 'Table Name';
select table_schema as "Database Name",
sum(data_length+index_length)/1024/1024 as "Database Size in MB"
from information_schema.tables
where table_schema = 'Database Name';
select table_name as "Table Name",
sum(data_length+index_length)/1024/1024 as "Table Size in MB"
from information_schema.tables
where table_schema = 'Database Name'
group by table_name
order by 2 desc;
Size of all databases in descending order:
select table_schema as "Database Name",
sum(data_length+index_length)/1024/1024 as "Database Size in MB"
from information_schema.tables
group by table_schema
order by 2 desc;
This script is enough for us to get things done. I am providing here the table description of the information_schema.tables for more understanding. The table description can be displayed by running DESCRIBE command.
mysql> desc information_schema.tables;
+-----------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512) | NO | | | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| TABLE_TYPE | varchar(64) | NO | | | |
| ENGINE | varchar(64) | YES | | NULL | |
| VERSION | bigint(21) unsigned | YES | | NULL | |
| ROW_FORMAT | varchar(10) | YES | | NULL | |
| TABLE_ROWS | bigint(21) unsigned | YES | | NULL | |
| AVG_ROW_LENGTH | bigint(21) unsigned | YES | | NULL | |
| DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
| INDEX_LENGTH | bigint(21) unsigned | YES | | NULL | |
| DATA_FREE | bigint(21) unsigned | YES | | NULL | |
| AUTO_INCREMENT | bigint(21) unsigned | YES | | NULL | |
| CREATE_TIME | datetime | YES | | NULL | |
| UPDATE_TIME | datetime | YES | | NULL | |
| CHECK_TIME | datetime | YES | | NULL | |
| TABLE_COLLATION | varchar(32) | YES | | NULL | |
| CHECKSUM | bigint(21) unsigned | YES | | NULL | |
| CREATE_OPTIONS | varchar(255) | YES | | NULL | |
| TABLE_COMMENT | varchar(2048) | NO | | | |
+-----------------+---------------------+------+-----+---------+-------+
21 rows in set (0.00 sec)
I hope this post will help you to complete your task quickly. Please write your comments on this post and let me know, if there are other simple methods to achieve this task. Thanks for your time.
