Display the size of all tables in a MySQL Database

Below is a simple (and handy) SQL that returns the size of each table in a mySQL database:

SELECT CONCAT(table_schema, ‘.’, table_name),
CONCAT(ROUND(table_rows / 1000000, 2), ‘M’) rows,
CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), ‘G’) DATA,
CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), ‘G’) idx,
CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), ‘G’) total_size,
ROUND(index_length / data_length, 2) idxfrac
FROM information_schema.TABLES
ORDER BY data_length + index_length DESC
LIMIT 10;

You would typically use this to see if a table containing logging data has grown too big as that is one of the most common issue with many web apps.

And now the output :

+—————————————+——-+——-+——-+————+———+ | CONCAT(table_schema, ‘.’, table_name) | rows | DATA | idx | total_size | idxfrac | +—————————————+——-+——-+——-+————+———+ |wptest.wp_postmeta | 0.10M | 0.01G | 0.01G | 0.02G | 1.05 | |wptest.wp_posts | 0.00M | 0.00G | 0.00G | 0.00G | 0.23 | |wptest.wp_comments | 0.00M | 0.00G | 0.00G | 0.00G | 0.33 | |wptest.wp_options | 0.00M | 0.00G | 0.00G | 0.00G | 0.08 | |wptest.wp_answers | 0.00M | 0.00G | 0.00G | 0.00G | 0.00 | |wptest.wp_commentmeta | 0.00M | 0.00G | 0.00G | 0.00G | 0.50 | |wptest.wp_usermeta | 0.00M | 0.00G | 0.00G | 0.00G | 0.67 | |wptest.wp_users | 0.00M | 0.00G | 0.00G | 0.00G | 2.00 | |wptest.wp_terms | 0.00M | 0.00G | 0.00G | 0.00G | 2.00 | |wptest.wp_term_taxonomy | 0.00M | 0.00G | 0.00G | 0.00G | 2.00 | +—————————————+——-+——-+——-+————+———+ 10 rows in set (0.22 sec) mysql>

 

Total DB Size

The following query returns the size of the entire Database:

SELECT table_schema "Data Base Name",
    sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",
    sum( data_free )/ 1024 / 1024 "Free Space in MB"
FROM information_schema.TABLES
GROUP BY table_schema ;
Advertisements