UPWORK/ODESK guru: Tips and Tricks for clients

skype: upwork.link , email: info@upwork.link

How to get the sizes of the tables of a mysql database.

without comments

You can use this query :

mysql> select table_name as 'Table', round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB"
from information_schema.tables where table_schema = 'database' and table_name = 'log';
+----------+------------+
| Table    | Size in MB |
+----------+------------+
| log      |    2357.02 |
+----------+------------+
1 row in set (0.00 sec)

mysql> 

… in order to know the size of a table.

And that:


[root@server /]# mysql -u root -e 'select table_name as "Table", table_schema as "Database", engine as "Engine", round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" from tables' information_schema
+---------------------------------------+--------------------+--------+------------+
| Table                                 | Database           | Engine | Size in MB |
+---------------------------------------+--------------------+--------+------------+
| vendors_received_bookings             | manager            | MyISAM |       0.14 |
| xml_log                               | manager            | InnoDB |   16746.47 |
| channels_xml_log                      | channel_log        | InnoDB |   12803.64 |
| channels_xml_log_cnt                  | channel_log        | MyISAM |       0.00 |
...

OR

mysql> select table_name as 'Table', round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB"
from information_schema.tables where table_schema = 'database';
+-----------------------------+------------+
| Table                       | Size in MB |
+-----------------------------+------------+
| all                         |      12.91 |
| yes                         |       0.07 |
...
| log                         |   13454.39 |
+-----------------------------+------------+
64 rows in set (1.52 sec)

mysql> 

… in order to know the size of the tables of a database.

Additional You can get size of the mysql databases as following.

mysql> select table_schema "DB name", round(sum(data_length + index_length) / 1024 / 1024, 1)
"DB Size in MB"  from information_schema.tables group by table_schema;
+--------------------+---------------+
| DB name            | DB Size in MB |
+--------------------+---------------+
| database1          |       35438.7 |
| base_log           |       13314.7 |
| information_schema |           0.0 |
| mysql              |           0.6 |
+--------------------+---------------+
4 rows in set (0.88 sec)

mysql>

Written by spectre

November 18th, 2013 at 11:34 am

Posted in Mysql

Leave a Reply

*