How to Find Table & Database Size in MySQL

mysql-database-size
mm
Written By:- Santosh Prasad

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.

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:)

About Author

mm

Santosh Prasad

Hi! I'm Santosh and I'm here to post some cool article for you. If you have any query and suggestion please comment in comment section.

Other Post by Santosh Prasad

Visit All Post

Related Article

You may also Like

Leave a Comment

Shares