Knowing MySQL databases’ and tables’ size using two requests

MySQLHello,

I guess that, as I do, you appreciate to know MySQL database’ and tables size. It is possible using simple requests based on the database information_schema

  • Knowing databases’ size
SELECT table_schema "Name of the DB", SUM( data_length + index_length)
"Taille" FROM information_schema.TABLES GROUP BY table_schema ;

It is of course possible to filter this request to get only some databases based on a “LIKE” or “IN” or “=”. Here is an example using “LIKE“:

WHERE table_schema LIKE "%schema of your base%"

or

 table_schema IN ('dbname1',dbname2'...)

Data returned are in bytes, I choose not to convert them in MB,GB etc.. You can add your a division to convert data returned.

  •  Knowing size of tables ( by databases ). I choose to keep the possibility to filter on multiple databases. I also keep table that have at least 1 row :
SELECT ENGINE,table_schema, table_name, AVG_ROW_LENGTH as Entry_data_size,TABLE_ROWS as Rows,DATA_LENGTH as Data_Size,INDEX_LENGTH as Index_Size,DATA_LENGTH+INDEX_LENGTH as Total_size, ROUND(DATA_LENGTH/(DATA_LENGTH+INDEX_LENGTH),2) as Ratio 
FROM information_schema.TABLES where TABLE_NAME like "%schema-of-table%" and table_schema like'%schema-of-databases%' and TABLE_ROWS >0 
GROUP BY table_schema,table_name 
ORDER BY TABLE_ROWS desc;

Data returned are in bytes, I choose not to convert them in MB,GB etc.. You can add your a division to convert data returned.

I hope these requests will help you administrating your database  !

Etienne.

PS: If you think that this article is not precise enough or contain errors, don’t hesitate to contact me : contact@etiennegautier.net

Tags: , , , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.