Wednesday, December 3, 2008

MySQL Table and Schema Size

I found these 2 commands for finding the size of a table or whole schema on MySQL mailing list (unable to recollect the URL)


Query To Find the size of a schema

#Query to find the size of a database schema (replace YOUR_SCHEMA_NAME with appropriate name)

#Run this query without selecting any schema

SELECT s.schema_name, CONCAT(IFNULL(ROUND((SUM(t.data_length)+SUM(t.index_length)) /1024/1024,2),0.00),"Mb") total_size, CONCAT(IFNULL(ROUND(((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/1024/1024,2),0.00),"Mb") data_used, CONCAT(IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00),"Mb") data_free, 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 = "YOUR_SCHEMA_NAME" GROUP BY s.schema_name ORDER BY pct_used DESC\G;

-------------------------------------------------------

Query To Find the size of a table

# Query to find size of a particular table (replace YOUR_TABLE_NAME with appropriate name)

# Run this after choosing the schema

show table status like 'YOUR_TABLE_NAME'