Table Sizes

Posted: 21st December 2008 by Darren Cassar in Beginner
Tags: , ,

During the course of my daily work I occasionally search for mysql queries which are cool and helpful. I once found the following query on http://forge.mysql.com/:

SELECT table_name article_attachment,
engine,
ROUND(data_length/1024/1024,2) total_size_mb,
ROUND(index_length/1024/1024,2) total_index_size_mb,
table_rows
FROM information_schema.tables
WHERE table_schema = ‘dbname’
ORDER BY 3 desc;

A generally  lite version is:

select table_schema, table_name, (data_length)/pow(1024,2) AS ‘Data Size in Meg’, (index_length)/pow(1024,2) AS ‘Index Size in Meg’  from tables order by 3 desc;

You can add or remove columns etc and but this query shows the table size (data wise) index size, approx number of rows, size in MB etc. If you would like to know what else is available to add to this query, just do a “desc tables” while using the informations chema (database).

VN:F [1.9.22_1171]
Rating: 9.0/10 (1 vote cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Table Sizes, 9.0 out of 10 based on 1 rating

  1. Nicklas Westerlund says:

    “but this query shows the table size (data wise) index size, approx number of rows, size in KB etc”

    Darren,

    data_length is in bytes, so when dividing by pow(1024,2) you get MB, not KB, right?

  2. right Nicklas, thanks for pointing this out. It’s now corrected :). As you correctly noted, one can change the pow parameters in order to obtain MB, GB or TB (if need be).
    Cheers
    Darren

*