How can I found out which tables consume most space?

Following a question I got from one of community members

 

"Do you now a fast way to find out, on a large mysql db, which tables consume most space?"

Since MySQL 5.0 a new virtual schema was added to the MySQL database INFORMATION_SCHEMA (apart to the well known mysql schema).

INFORMATION_SCHEMA is the information database, the place that stores information about all the other databases that the MySQL server maintains.

One may query the  information schema to get the table side, for example list the top 10 largest tables.
 

SELECT  TABLE_SCHEMA,TABLE_NAME,DATA_LENGTH
FROM  INFORMATION_SCHEMA.TABLES WHERE
TABLE_SCHEMA <> 'information_schema' and TABLE_SCHEMA <>'mysql'
ORDER BY DATA_LENGTH DESC LIMIT 10
 

 

+--------------+---------------+-------------+
| TABLE_SCHEMA | TABLE_NAME    | DATA_LENGTH | 
+--------------+---------------+-------------+ 
| test         | archive       |   847440000 | 
| world        | City          |      273293 | 
| world        | Country       |       62379 | 
| world        | CountryLanguag|       38376 | 
| test         | engine2       |       16384 | 
| test         | aduit         |        4590 | 
| test         | test          |          49 | 
| test         | engine        |           0 | 
+--------------+---------------+-------------+

[root@haimr test]# ll | grep archive
-rw-rw---- 1 mysql mysql      8622 2009-07-02 17:15 archive.frm
-rw-rw---- 1 mysql mysql 847440000 2009-07-05 08:40 archive.MYD
-rw-rw---- 1 mysql mysql 435197952 2009-07-05 08:40 archive.MYI
 

Thank you for your interest!

We will contact you as soon as possible.

Send us a message

Oops, something went wrong
Please try again or contact us by email at info@tikalk.com