In this post I’ll show you two interesting utilities from common_schema framework: calculete the size by engine (MyIsam, InnoDB, Memory, …), and the size of each schema.
Just type this:
SELECT * FROM common_schema.data_size_per_engine;
SELECT * FROM common_schema.data_size_per_schema;
In my opinion, it could be more “teadable” for the user if the number would be in Mb, instead of bytes, but it’s my personal opinion.
data_size_per_engine
data_size_per_schema
An alternative query to calculate the size for each schema is:
SELECT table_schema, ROUND(SUM(data_length+index_length)/1024/1024) AS total_mb,
ROUND(SUM(data_length)/1024/1024) AS data_mb,
ROUND(SUM(index_length)/1024/1024) AS index_mb
FROM INFORMATION_SCHEMA.tables
GROUP BY table_schema ;
Alternative data_size_per_schema2
If you consider this query as an interesting one, you should think seriously in creating your own database to collect all these querys and functions that allows you to manage your MariaDB or MySQL server, and to know the state of the server.
I hope it can be useful for you, and … have a nice day!