Pages

Welcome to My Blog

This is to share my IT experience with friends all around the world.
I have been working in Linux Fedora Systems for more than 8 years. Its fun to share knowledge and learn..
As everyone knows when a problem arises in your systems "googling" is the way that many depend on..

All the posts here are my working experiences during my working life.. So you can count on it..

I have added the references where I got help in solving IT issues


Monday, July 13, 2020

Getting the table list with sizes in MySQL




We may need to get the table sizes of the tables in a mysql database from the mysql command prompt.

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

Here <database_name> needs to be changed to your database name. Also you can get the sizes in MB or GB by adding /1024 required times. For example, to get the size in MB the code has to be changed as follows;

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

Good Reference:

https://chartio.com/resources/tutorials/how-to-get-the-size-of-a-table-in-mysql/
 

No comments:

Post a Comment