How to find database and table size in MySQL?

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.







Size of a specific table:

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';
Size of a specific database:

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';








Size of all tables in a database with descending order:

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.

20 comments:

  1. Great knowledge, do anyone mind merely reference back to it Apartment intercom system upgrade.

    ReplyDelete
  2. Great job for publishing such a beneficial web site. Your web log isn’t only useful but it is additionally really creative too. There tend to be not many people who can certainly write not so simple posts that artistically. Continue the nice writing moblogtech.com

    ReplyDelete
  3. Truly, this article is really one of the very best in the history of articles. I am a antique ’Article’ collector and I sometimes read some new articles if I find them interesting. And I found this one pretty fascinating and it should go into my collection. Very good work!All note able

    ReplyDelete
  4. Juggling work, home and community lives, parents now rely heavily on communication, information and transportation technology to make their lives faster and more efficient.curso scrum master

    ReplyDelete
  5. The importance of technological innovation in today's competitive economy is very clear, as today the worldwide economy depends on technology and technological innovation to an extraordinary degree.Obsolete electronic components

    ReplyDelete
  6. , they prompt that innovation is an essential piece of a college understudies life since they need to do must everything on the web from applying for school or college, looking and enrolling for classes, streaming microphone

    ReplyDelete
  7. I Like Technology. I'm yielding all the great and fun things that PC based technology has brought into our lives; I'll not face that conflict. In addition to the fact that I would lose any contention against the superb increments technology has made to our lives, I would be battling against myself.vps hosting

    ReplyDelete
  8. Great job for publishing such a beneficial web site. Your web log isn’t only useful but it is additionally really creative too. Big data

    ReplyDelete
  9. I wanted to thank you for this great read!! I definitely enjoying every little bit of it I have you bookmarked to check out new stuff you post. Symptoms of Not Drinking Enough Water

    ReplyDelete
  10. You can use the energy sources created by an evolving market to motivate prospects to buy your solution. Persuading people to try out a new technology is an uphill battle. best programming keyboard

    ReplyDelete
  11. So Intel Inside is Intel's core technology which, My Washer is Leaking from the Bottom when integrated into other lesser known brands, has the power to sell the latter to great effect. It might though be cripplingly expensive for the host. Intel will sell your product, but at a price.

    ReplyDelete
  12. https://www.mycestsolution.com/2019/10/proteus-arduino-vsm-library-by-mycest.html?showComment=1591727521026#c2597506754287567555

    ReplyDelete
  13. In addition, I am convinced that innovative ideas in technologies have created a completely new life, ac motor repair which poses new challenges for our country. How we will cope with these tasks depends on the future of our country.

    ReplyDelete
  14. Now, smartphones have broadened the scope of communication which is not just limited to making long distance calls. Due to the changes in technology, homes for sale in milton the reliance upon technological gadgets has increased.

    ReplyDelete
  15. You want to use the technology that works the best for your project and will be the most beneficial. Key Replacement Make sure before committing to one company that you ask what technologies the company works in and you should be able to tell from their answer their willingness to work in different environments.

    ReplyDelete
  16. Love to use tech? and gotta buy some new gadgets? try our site we will provide high quality gadgets .



    Mercado Tech

    ReplyDelete
  17. My experience is: ignore technology and "Real Recruiter" or not your business is doomed, combination weigher it will not progress far into the future as a viable entity. You will be replaced.

    ReplyDelete
  18. This is my first time i visit here and I found so many interesting stuff in your blog especially it's discussion, thank you. selfie ring light

    ReplyDelete
  19. In the event that you have a SIM card, you don't have to acquire a telephone for each new number or area. Rather you place your SIM card on your telephone and afterward that telephone turns into your cell phone with your own number. atm skimmer for sale

    ReplyDelete