Contents...
As a database administrator you need to know the size of each MySQL table to optimise them. To make some free space in your disk. You can do this checking the manually the size of each table and database in the MySQL data directory, by default you can find it on “/var/lib/mysql/” location. But using this tutorial you can find all table and database size executing one command. Follow the below command to find the table and database sizes.
Suggested Read: Basic Mysql Commands For Database Administrator
Listing MySQL Table Size
You can use below command in your MySQL shell:
Listing All Table Zize In KB
Follow the below command to listing all table size in KB.
SELECT table_schema AS DB_NAME, TABLE_NAME, (DATA_LENGTH+INDEX_LENGTH)/1024 AS TABLE_SIZE_in_KB FROM information_schema.TABLES;
mysql> SELECT table_schema AS DB_NAME, TABLE_NAME, (DATA_LENGTH+INDEX_LENGTH)/1024 AS TABLE_SIZE_in_KB FROM information_schema.TABLES; +--------------------+----------------------------------------------------+------------------+ | DB_NAME | TABLE_NAME | TABLE_SIZE_in_KB | +--------------------+----------------------------------------------------+------------------+ | webtest | sagartb_catalog_product_option_type_title | 48.0000 | | webtest | sagartb_catalog_product_option_type_value | 32.0000 | | webtest | sagartb_catalog_product_super_attribute | 32.0000 | | webtest | sagartb_catalog_product_super_attribute_label | 32.0000 | | webtest | sagartb_catalog_product_super_attribute_pricing | 32.0000 | | webtest | sagartb_catalog_product_super_link | 48.0000 | | web3_te | ftp_skin_replacements | 62.9727 | | web3_te | ftp_skin_templates | 1332.3984 | | web3_te | ftp_skin_templates_cache | 2.0000 | | web3_te | ftp_skin_templates_previous | 1220.0938 | | web3_te | ftp_skin_templates_previous | 1220.0938 |
Listing All Table Size In MB
Follow the below command to listing all table size in MB.
SELECT table_schema AS DB_NAME, TABLE_NAME, (DATA_LENGTH+INDEX_LENGTH)/1024/1024 AS TABLE_SIZE_in_MB FROM information_schema.TABLES;
mysql> SELECT table_schema AS DB_NAME, TABLE_NAME, (DATA_LENGTH+INDEX_LENGTH)/1024/1024 AS TABLE_SIZE_in_MB FROM information_schema.TABLES; +--------------------+----------------------------------------------------+------------------+ | DB_NAME | TABLE_NAME | TABLE_SIZE_in_MB | +--------------------+----------------------------------------------------+------------------+ | webtest | sagartb_log_url | 610.91451550 | | webtest | sagartb_log_url_info | 3491.07292557 | | webtest | sagartb_log_visitor | 4588.91913033 | | webtest | sagartb_log_visitor_info | 2471.38349915 | | webtest | sagartb_report_event | 1136.64062500 | | webtest | sagartb_review_detail | 33.12500000 | | webtest | ftp_posts | 853.07502365 | | webtest | ftp_message_posts | 31.20442581 | | web3_te | ftp_message_topic_user_map | 10.62506485 | | web3_te | ftp_message_topics | 9.57638550 | +--------------------+----------------------------------------------------+------------------+ 10 rows in set (1.52 sec) mysql>
Listing All Tables Having Size Bigger Than 100 KB
Now here we are going to list all tables having size bigger than 100 KB.
SELECT table_schema AS DB_NAME, TABLE_NAME, (DATA_LENGTH+INDEX_LENGTH)/1024 AS TABLE_SIZE_in_KB FROM information_schema.TABLES WHERE (DATA_LENGTH+INDEX_LENGTH)/1024 > 100;
mysql> SELECT table_schema AS DB_NAME, TABLE_NAME, (DATA_LENGTH+INDEX_LENGTH)/1024 AS TABLE_SIZE_in_KB FROM information_schema.TABLES WHERE (DATA_LENGTH+INDEX_LENGTH)/1024 > 100; +------------------+----------------------------------------------+------------------+ | DB_NAME | TABLE_NAME | TABLE_SIZE_in_KB | +------------------+----------------------------------------------+------------------+ | life | bookmarks | 256.0000 | | life | saves | 176.0000 | | life | sites | 112.0000 | | life | stylegrids | 128.0000 | | life | topics | 208.0000 | | lifedata_answers | answer_per | 368.0000 | | lifedata_answers | experts | 176.0000 | | lifedata_answers | links | 336.0000 | | lifedata_answers | personality | 128.0000 | | lifedata_answers | qa_posts | 384.0000 | | lifedata_answers | question | 192.0000 | | lifedata_answers | quiz | 160.0000 | | lifedata_answers | quotes | 192.0000 | +------------------+----------------------------------------------+------------------+ 13 rows in set (1.42 sec) mysql>
Listing Database Size
To list all database size you can follow the below command.
Listing All Database Size In KB
SELECT table_schema "DATABASE_NAME", SUM(data_length + index_length) / 1024 "DATABASE SIZE IN KB" FROM information_schema.tables GROUP BY table_schema;
mysql> SELECT table_schema "DATABASE_NAME", SUM(data_length + index_length) / 1024 "DATABASE SIZE IN KB" FROM information_schema.tables GROUP BY table_schema; +--------------------+---------------------+ | DATABASE_NAME | DATABASE SIZE IN KB | +--------------------+---------------------+ | life | 3392.0000 | | information_schema | 8.0000 | | lifedata_answers | 3236.0703 | | lifedata_blog | 2320.0000 | | lifedata_topic | 817.0000 | | mdirectory | 786.9375 | | mysql | 673.2227 | | tpsupport | 8146.7500 | | test | 235.9326 | | test1 | 1.0000 | | thushar | NULL | | web_test | 13316335.3340 | | web_pest | 28122.6885 | | web1_test1_db | 4808.8516 | +--------------------+---------------------+ 14 rows in set (1.38 sec) mysql>
Listing All Database Zize In MB
SELECT table_schema "DATABASE_NAME", SUM(data_length + index_length) / 1024 / 1024 "DATABASE SIZE IN MB" FROM information_schema.tables GROUP BY table_schema;
mysql> SELECT table_schema "DATABASE_NAME", SUM(data_length + index_length) / 1024 / 1024 "DATABASE SIZE IN MB" FROM information_schema.tables GROUP BY table_schema; +--------------------+---------------------+ | DATABASE_NAME | DATABASE SIZE IN MB | +--------------------+---------------------+ | life | 3.31250000 | | information_schema | 0.00781250 | | lifedata_answers | 3.16022491 | | lifedata_blog | 2.26562500 | | lifedata_topic | 0.79785156 | | mdirectory | 0.76849365 | | mysql | 0.65744400 | | tpsupport | 7.95581055 | | test | 0.23040295 | | test1 | 0.00097656 | | thushar | NULL | | web_test | 13004.24014759 | | web_pest | 27.46356297 | | web1_test1_db | 4.69614410 | +--------------------+---------------------+ 14 rows in set (1.32 sec)
Listing All Database Size In GB
SELECT table_schema "DATABASE_NAME", SUM(data_length + index_length) / 1024 / 1024 / 1024 "DATABASE SIZE IN GB" FROM information_schema.tables GROUP BY table_schema;
mysql> SELECT table_schema "DATABASE_NAME", SUM(data_length + index_length) / 1024 / 1024 / 1024 "DATABASE SIZE IN GB" FROM information_schema.tables GROUP BY table_schema; +--------------------+---------------------+ | DATABASE_NAME | DATABASE SIZE IN GB | +--------------------+---------------------+ | life | 0.003234863281 | | information_schema | 0.000007629395 | | lifedata_answers | 0.003086157143 | | lifedata_blog | 0.002212524414 | | lifedata_topic | 0.000779151917 | | mdirectory | 0.000750482082 | | mysql | 0.000642035156 | | tpsupport | 0.007769346237 | | test | 0.000225002877 | | test1 | 0.000000953674 | | thushar | NULL | | web_test | 12.699455166236 | | web_pest | 0.026819885708 | | web1_test1_db | 0.004586078227 | +--------------------+---------------------+ 14 rows in set (1.35 sec)
Listing The Zize Of Specific Database
SELECT table_schema "database", sum(data_length + index_length)/1024/1024/1024 "size in GB" FROM information_schema.TABLES WHERE table_schema='test3' GROUP BY table_schema;
mysql> SELECT table_schema "database", sum(data_length + index_length)/1024/1024/1024 "size in GB" FROM information_schema.TABLES WHERE table_schema='test3' GROUP BY table_schema; +------------+-----------------+ | database | size in GB | +------------+-----------------+ | test3 | 31.398088419453 | +------------+-----------------+ 1 row in set (0.03 sec)
I hope this article will help to find the Table and Database size in MySQL. If you have any queries and problem please comment in comment section.
Thanks:)
Thank you! for visiting LookLinux.
If you find this tutorial helpful please share with your friends to keep it alive. For more helpful topic browse my website www.looklinux.com. To become an author at LookLinux Submit Article. Stay connected to Facebook.
Leave a Comment