CODEWITHSUNDEEP
sqlmysql
Cory
Cory

Reputation: 2837

How do you find out the total size of the data in MySQL database?

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.

Upvotes: 7

Views: 5730

Answers (3)

Cody Caughlan
Cody Caughlan

Reputation: 32758

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" ?

Upvotes: 2

Nix
Nix

Reputation: 58582

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;

Upvotes: 8

Orbling
Orbling

Reputation: 20612

Try looking at the information given by

SHOW TABLE STATUS FROM dbname;

The Data_length column should be of interest.

See: http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html

Or if you need access within the context of a normal query, then the contents of INFORMATION_SCHEMA.TABLES tables can help. (See: http://dev.mysql.com/doc/refman/5.1/en/tables-table.html)

Upvotes: 1

Related Questions