Main Tutorials

How to calculate the MySQL database size

As i know, there are two ways to calculate MySQL database size.

1. SQL script

Sum up the data_length + index_length is equal to the total table size.

  1. data_length – store the real data.
  2. index_length – store the table index.

Here’s the SQL script to list out the entire databases size


SELECT table_schema "Data Base Name", sum( data_length + index_length) / 1024 / 1024 
"Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema ;

Another SQL script to list out one database size, and each tables size in detail


SELECT table_name, table_rows, data_length, index_length, 
round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"
FROM information_schema.TABLES where table_schema = "schema_name";

2. Locate the MySQL stored data

Access this article to find out where does MySQL database saved the data.

Windows
Locate the MySQL ibdata1 file, right click on the file and click the properties, see the size? 🙂

Linux
Locate the MySQL ibdata1 file


mkyong@myserver:/var/lib/mysql$ ls -lh
total 1.5G
drwx------ 2 mysql mysql 4.0K 2009-08-26 13:36 mydatabase
-rw-r--r-- 1 root  root     0 2009-08-19 09:39 debian-5.0.flag
-rw-rw---- 1 mysql mysql 1.5G 2009-08-27 17:32 ibdata1
-rw-rw---- 1 mysql mysql 5.0M 2009-08-27 17:32 ib_logfile0
-rw-rw---- 1 mysql mysql 5.0M 2009-08-27 17:32 ib_logfile1
drwxr-xr-x 2 mysql root  4.0K 2009-08-19 11:19 mysql
-rw------- 1 root  root     6 2009-08-19 09:39 mysql_upgrade_info

About Author

author image
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

Subscribe
Notify of
27 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments
Vicky Khan
7 years ago

is the above query result includes size of TEXT and BLOB columns?

Marcelo Ancieta
5 years ago
Reply to  Vicky Khan

Yes

Vašek Kasal
8 years ago

Thx for sharing.
One thing:
Your result looks like MiB, not MB unit – (data_length + index_length) / 1024 / 1024
MB should be calculated as – (data_length + index_length) / 1000 / 1000

Roy
8 years ago

This provides more data and better resembles the entire schema, where the original poster only returned one row.

select table_schema, table_name, table_rows, data_length, index_length,
round(sum(data_length + index_length)/1024/1024,2) AS “Size in MB”
from information_schema.TABLES where table_schema = “schema_name”
GROUP BY table_schema, table_name;

Bhavana kl
9 years ago

Can any one help me how to set database size limit.

Ramesh
10 years ago

How to get data_length index_length of specific table? Is it possible. please help

Zain
6 years ago
Reply to  Ramesh

SELECT data_length , index_length ,round(((data_length + index_length) / 1024 / 1024), 2) Size in MB FROM information_schema.TABLES WHERE table_schema = “database name” AND table_name = “table name”

Avishek
9 years ago
Reply to  Ramesh

This can be done without SQL query as well, best explained here http://mysqldevs.blogspot.in/2014/04/mysql-database-size-stored-data.html?utm_source=BP_recent

NoName
10 years ago

Issue: Checking file size and sum of result from the query yields two different values…

Dheeraj
8 years ago
Reply to  NoName

same with me

Neha
10 years ago

This is exactly what I was looking for. Your blog always helps when I am looking to either understand a topic or when I am looking for a solution to an exact problem. Good job!!

printable coupon code
10 years ago

It’s actually very complicated in this active life to listen news on Television, thus I just use world wide web for that reason, and obtain the newest news.

jennifer
10 years ago

Hi there, just became alert to your blog through Google, and found that it’s truly informative. I am going to watch out for brussels. I’ll appreciate if you continue this
in future. Numerous people will be benefited from your writing.
Cheers!

rahul
11 years ago

thanks, you rock, this really helped, made my task a cinch.

Juan Capristán
12 years ago

Nice, just what I was looking for. It’s obvious from your post, but just for completeness, the query to get the total size of a particular DB:

SELECT sum(ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024),2)) "Size in MB"
FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = "your_db_name";

Thanks!.

Luca Mastrobuono
10 years ago
Reply to  mkyong

a similar one maybe:

SELECT TABLE_ROWS,round(((DATA_LENGTH+INDEX_LENGTH)/1024 /1024),2) as “Size in MB”,TABLE_SCHEMA,TABLE_NAME from information_schema.TABLES;

Bernd Adamowicz
12 years ago

Your select statements for displaying table- and database-sizes are great. Exactly what I was looking for.

Thanks,
Bernd

Mickey
13 years ago

A simple trick with 1 line total;
Just sum up all of the files in the database path
this way you get a true measure of the HD storage.

ls /var/lib/mysql/ -lh|grep total

Total 21M

PJ Brunet
11 years ago
Reply to  Mickey

Thanks for the tip but this will always say 21M.

You need to specify the subdirectory or add “-R” if you want the full picture, for example:

ls -R /var/lib/mysql/ -lh|grep total
total 21M
total 4.2M
total 247M
total 68K
total 16M
total 984K
total 371M

In this case I have 6 databases in 6 subdirectories.

Matt
11 years ago
Reply to  PJ Brunet

We could also do:

du -sh /var/lib/mysql/*

That should list the total size on disk per database.

I know this post is very old but I check back here every now and then. 🙂

Kai
10 years ago
Reply to  Matt

Getting the size using file system utilities only works nice for databases using MyISAM or the innodb_file_per_table option. If the InnoDB data is stored within the same ibdata file(s) then there is no way of determining the amount stored for each database.

But the original SQL works like a charm 🙂

Ravy
14 years ago

I want to calculate real data size per row of table not table

daniel
14 years ago

ibdata1 is the sum of all databases that you have on the installation and that can be more than one database.