Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

We have a database that has grown organically over the past year. How can we quickly analyze the space used by each table? We would like to consider the indexes as well.

share|improve this question
up vote 3 down vote accepted

When I am using innodb tables in my databases I like to use the innodb_file_per_table setting. This allows me to get a quick idea of what is going on with ls like chaos suggested.

This statement may give you a pretty good idea about how much space you are using.

use information_schema;
share|improve this answer

Have I got some crazy queries for you to use. I wrote these two years ago and still use them today. Give them a Try !!!

Here is a query to sum up all data across all storage engines

SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),
'B') "Index Size", CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),
17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Table Size"
FROM (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema') AND

Here is a query to sum up all data across all databases

SELECT DBName,CONCAT(LPAD(FORMAT(SDSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",CONCAT(LPAD(FORMAT(SXSize/
POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",
CONCAT(LPAD(FORMAT(STSize/POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),
'B') "Total Size" FROM (SELECT IFNULL(DB,'All Databases') DBName,SUM(DSize) SDSize,
SUM(XSize) SXSize,SUM(TSize) STSize FROM (SELECT table_schema DB,
data_length DSize,index_length XSize,data_length+index_length TSize
FROM information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema')) AAA GROUP BY DB WITH ROLLUP) AA,
(SELECT 3 pw) BB ORDER BY (SDSize+SXSize);

Here is a query to sum up all data across all databases grouped by storage engine

SELECT Statistic,DataSize "Data Size",IndexSize "Index Size",TableSize "Table Size"
FROM (SELECT IF(ISNULL(table_schema)=1,10,0) schema_score,
IF(ISNULL(engine)=1,10,0) engine_score,IF(ISNULL(table_schema)=1,
'ZZZZZZZZZZZZZZZZ',table_schema) schemaname,
IF(ISNULL(B.table_schema)+ISNULL(B.engine)=2,"Storage for All Databases",
CONCAT("Storage for ",B.table_schema),
CONCAT(B.engine," Tables for ",B.table_schema))) Statistic,
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),
' ',SUBSTR(' KMGTP',pw+1,1),'B') DataSize,CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') TableSize
FROM (SELECT table_schema,engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema') AND
engine IS NOT NULL GROUP BY table_schema,engine WITH ROLLUP) B,
(SELECT 3 pw) A) AA ORDER BY schemaname,schema_score,engine_score;

CAVEAT: You will notice that at the tail end of all these queries is an inline SELECT that looks like this: (SELECT 3 pw)

The number 3 causes the report to come out in GigaBytes.
In fact here is a list of the numbers and the units of the report it uses:

  • (SELECT 0 pw) Reports in Bytes
  • (SELECT 1 pw) Reports in Kilobytes
  • (SELECT 2 pw) Reports in Megabytes
  • (SELECT 3 pw) Reports in Gigabytes
  • (SELECT 4 pw) Reports in Terabytes
  • (SELECT 5 pw) Reports in Petabytes (I have never used this setting but it's there in case you reach that number someday)

Enjoy !!!

share|improve this answer
Why, why is this not more upvoted for its sheer awesomeness? Cheers for those, very handy! – Neil Masters Apr 14 '15 at 8:07

Try using a tool like this to view your MySQL instance in an easy-to-read format:

share|improve this answer

Mysql has some built in functionality

use <datbase name>;
show table status;
share|improve this answer
ls -la /var/lib/mysql/*
share|improve this answer
That works fine for myisam tables, it doesn't do anything for innodb, which by default uses one file for all databases, tables, and indexes. – Zoredache Jun 4 '09 at 18:43

Have phpmyadmin installed on your server.

phpMyAdmin is a free software tool written in PHP intended to handle the administration of MySQL over the World Wide Web. phpMyAdmin supports a wide range of operations with MySQL. The most frequently used operations are supported by the user interface (managing databases, tables, fields, relations, indexes, users, permissions, etc), while you still have the ability to directly execute any SQL statement

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.