mysql

Sometimes you need to know the size of each mysql table to optimize them and gain some free space in the disk.
To do so, you can check manually the size of each table in the mysql datadir (generally it is /var/lib/mysql), or you can do even better by asking Mysql to do it for you.

Listing Mysql tables size

You can use the command below when connected to the mysql shell:

SELECT table_schema AS DB_NAME, TABLE_NAME, (DATA_LENGTH+INDEX_LENGTH)/1024 AS TABLE_SIZE_in_KB FROM information_schema.TABLES;

To list above tables 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;

In this blog, I am using KB as the unit because I have small tables. So depending on the size of your data you can adjust the unit.
To list all tables having size bigger than 1 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 > 1;

Example:

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

mysql-size

Listing databases size

To list each database size you can use the following command:

SELECT table_schema "DATABASE_NAME", SUM(data_length + index_length) / 1024 "DATABASE SIZE IN KB" FROM information_schema.tables GROUP BY table_schema;

Example:

mysql> SELECT table_schema "DATABASE_NAME", SUM(data_length + index_length) / 1024 "DATABASE SIZE IN KB" FROM information_schema.tables GROUP BY table_schema;
mysql-size-2

Or in MB:

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;

mysql-size-3

I hope that this blog helped you. Please visit our website for other interesting blogs and feel free to leave your feedbacks and thoughts. Till next time!

Post Views: 14,302
The following two tabs change content below.
My Facebook profile

ZIADI Mohamed Ali

I am a hands-on, competent Linux system engineer with 9 years’ experience. I have a strong performance background in wide variety of professional Linux system support including monitoring, configuration, troubleshooting and maintenance. I have worked on numerous projects from concept to completion. A specialist in LAMP platforms, I take pride in administrating Linux systems and regularly refresh my skills to ensure I keep up with ongoing developments and new technologies.