Menu
How do you calculate the total size of the database in MySQL?
P.S. Total size in term of how much disk space is using.
443
You can find this with: select sum(bytes)/1024/1024 size_in_mb from dba_data_files; But not all this space is necessarily allocated. There could be sections of these files that are not used.
To counts all of the rows in a table, whether they contain NULL values or not, use COUNT(*). That form of the COUNT() function basically returns the number of rows in a result set returned by a SELECT statement.
SQL Server LEN() FunctionThe LEN() function returns the length of a string. Note: Trailing spaces at the end of the string is not included when calculating the length. However, leading spaces at the start of the string is included when calculating the length.
This link has a pretty intense query... that will give you more than you need...:
SELECT s.schema_name,
CONCAT(IFNULL(ROUND((SUM(t.data_length)+SUM(t.index_length)) /1024/1024,2),0.00)) total_size_in_MB,
CONCAT(IFNULL(ROUND(((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/1024/1024,2),0.00)) data_used_IN_MB,
CONCAT(IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00)) data_free_IN_MB,
IFNULL(ROUND((((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/((SUM(t.data_length)+SUM(t.index_length)))*100),2),0) pct_used,
COUNT(table_name) total_tables
FROM INFORMATION_SCHEMA.SCHEMATA s
LEFT JOIN INFORMATION_SCHEMA.TABLES t ON s.schema_name = t.table_schema
WHERE s.schema_name = 'abc' -- give your schema name
GROUP BY s.schema_name
ORDER BY pct_used DESC;
If you're looking for actual disk space usage what about just calculating the size of the mysql data directory using a filesystem utility like "du" ?
2
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
