How to Get True Size of MySQL Database?

Question

I would like to know how much space does my MySQL database use, in order to select a web host. I found the command SHOW TABLE STATUS LIKE 'table_name' so when I do the query, I get something like this:

Name       | Rows | Avg. Row Length | Data_Length | Index Length
----------   ----   ---------------   -----------   ------------
table_name   400          55            362000        66560

numbers are rounded.

So do I have 362000 or 400*362000 = 144800000 bytes of data for this table? And what does Index Length mean? Thanks !

Possible duplicate of [How to get size of mysql database?](http://stackoverflow.com/questions/1733507/how-to-get-size-of-mysql-database) — Mohammad, Nov 06 '16 at 12:39
This page will give you exact answer http://www.rathishkumar.in/2017/12/how-to-find-database-and-table-size-in-mysql.html — Rathish Kumar B, Dec 29 '17 at 12:43

score 301 · Answer 1 · edited Oct 31 '18 at 15:52

301

From S. Prakash, found at the MySQL forum:

SELECT table_schema "database name",
    sum( data_length + index_length ) / 1024 / 1024 "database size in MB",
    sum( data_free )/ 1024 / 1024 "free space in MB"
FROM information_schema.TABLES
GROUP BY table_schema;

Or in a single line for easier copy-pasting:

SELECT table_schema "database name", sum( data_length + index_length ) / 1024 / 1024 "database size in MB", sum( data_free )/ 1024 / 1024 "free space in MB" FROM information_schema.TABLES GROUP BY table_schema;

edited Oct 31 '18 at 15:52

sjas

18,644
14
87
92

answered Jan 16 '14 at 17:46

ron_dobley

3,029
1
14
2

7

What does the Free Space in MB means ? I have 100s of GB free but it reports less than 1 GB free. – Barth Feb 26 '15 at 10:57
1

@Barth as per the [documentation](https://dev.mysql.com/doc/refman/5.7/en/tables-table.html "The INFORMATION_SCHEMA TABLES Table") it means "free space in bytes for InnoDB tables". Unfortunately there's no explanation what *that* means :-\ – ckujau May 04 '15 at 19:08
15

Free space in bytes means exactly that - it means that amount of space could be reclaimed if you OPTIMIZE the table or rebuild the table from scratch. When databases store information on disks, they allocate it in blocks; deleting a record often frees a block between used blocks, and it's very expensive to shift all of the other blocks around to use that space up so it is marked as "free". The space may be used later by the database engine itself, or can be reclaimed using `OPTIMIZE TABLE foo` (MyISAM), or recreate + analyze for InnoDB tables. – razzed Dec 05 '15 at 16:35

score 97 · Answer 2 · edited Jul 01 '16 at 06:35

97

You can get the size of your Mysql database by running the following command in Mysql client

SELECT  sum(round(((data_length + index_length) / 1024 / 1024 / 1024), 2))  as "Size in GB"
FROM information_schema.TABLES
WHERE table_schema = "<database_name>"

edited Jul 01 '16 at 06:35

Jin Kwon

20,295
14
115
184

answered Jul 29 '15 at 06:10

minhas23

9,291
3
58
40

score 33 · Answer 3 · answered Feb 05 '13 at 19:03

33

If you use phpMyAdmin, it can tell you this information.

Just go to "Databases" (menu on top) and click "Enable Statistics".

You will see something like this:

phpMyAdmin screenshot

This will probably lose some accuracy as the sizes go up, but it should be accurate enough for your purposes.

answered Feb 05 '13 at 19:03

Radu Murzea

10,724
10
47
69

I am currently using 000webhost. It has phpMyAdmin but I couldn't find an "enable stats" checkbox. Is there (maybe) a query for that? – marvin Feb 05 '13 at 19:10
1

@marvin Well, it's possible that the webhost has an older version of phpMyAdmin installed. I don't know in which version this feature was introduced. – Radu Murzea Feb 05 '13 at 19:52
In your sidebar, click the Home icon at the top which takes you to the home page. There in the right window, click 'Databases'. It displays a one column table of databases, below that is a link called 'Enable Statistics' – Whip Jul 28 '16 at 11:49
I see a Note: Enabling the database statistics here might cause heavy traffic between the web server and the MySQL server. Enabling stats may impact performance of your applications. I would rather run the query suggested by minhas23 – ryadavalli Jun 07 '22 at 01:53

score 11 · Answer 4 · answered Jul 20 '16 at 16:41

11

if you want to find it in MB do this

SELECT table_schema                                        "DB Name", 
   Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" 
FROM   information_schema.tables 
GROUP  BY table_schema;

answered Jul 20 '16 at 16:41

jakson

255
3
10

This answer's SQL snippet appears to be a copy of http://stackoverflow.com/a/1733523/1200542 – Ariel Allon Apr 21 '17 at 19:10

score 4 · Answer 5 · edited Sep 22 '15 at 07:04

4

Basically there are two ways: query DB (data length + index length) or check files size. Index length is related to data stored in indexes.

Everything is described here:

http://www.mkyong.com/mysql/how-to-calculate-the-mysql-database-size/

edited Sep 22 '15 at 07:04

Anders

8,307
9
56
88

answered Feb 05 '13 at 18:57

MiGro

1,471
10
8

Well, this page is the source of my question. But according to this query, I have 0.5 MB, which leaves me pretty safe. I hope it is true :) Thanks ! – marvin Feb 05 '13 at 19:14

score 2 · Answer 6 · answered Oct 20 '18 at 13:51

If you want to find the size of all MySQL databases, us this command, it will show their respective sizes in megabytes;

SELECT table_schema "database", sum(data_length + index_length)/1024/1024 "size in MB" FROM information_schema.TABLES GROUP BY table_schema;

If you have large databases, you can use the following command to show the result in gigabytes;

SELECT table_schema "database", sum(data_length + index_length)/1024/1024/1024 "size in GB" FROM information_schema.TABLES GROUP BY table_schema;

If you want to show the size of only a specific database, for example YOUR_DATABASE_NAME, you could use the following query;

SELECT table_schema "database", sum(data_length + index_length)/1024/1024/1024 "size in GB" FROM information_schema.TABLES WHERE table_schema='YOUR_DATABASE_NAME' GROUP BY table_schema;

score 1 · Answer 7 · answered May 09 '16 at 11:07

1

None of the answers include the overhead size and the metadata sizes of tables.

Here is a more accurate estimation of the "disk space" allocated by a database.

SELECT ROUND((SUM(data_length+index_length+data_free) + (COUNT(*) * 300 * 1024))/1048576+150, 2) AS MegaBytes FROM information_schema.TABLES WHERE table_schema = 'DATABASE-NAME'

answered May 09 '16 at 11:07

Semra

2,787
28
26

5

Could you elaborate? Especially your constants `300` and `150`? – Melebius May 17 '16 at 13:23
Please, Could you elaborate? – Allan Andrade Jan 12 '17 at 01:28

score 0 · Answer 8 · edited May 23 '17 at 11:55

0

SUM(Data_free) may or may not be valid. It depends on the history of innodb_file_per_table. More discussion is found here.

edited May 23 '17 at 11:55

Community

1
1

answered Feb 22 '17 at 02:16

Rick James

135,179
13
127
222

score 0 · Answer 9 · answered Mar 26 '18 at 18:57

0

MySQL Utilities by Oracle have a command called mysqldiskusage that displays the disk usage of every database: https://dev.mysql.com/doc/mysql-utilities/1.6/en/mysqldiskusage.html

answered Mar 26 '18 at 18:57

ofrommel

2,129
14
19

score 0 · Answer 10 · answered Oct 17 '18 at 09:03

If you are using MySql Workbench, its very easy to get all details of Database size, each table size, index size etc.

Right Click on Schema
Select Schema Inspector option
It Shows all details of Schema size
Select Tables Tab to see size of each table.
Table size diplayed in Data Lenght column

How to Get True Size of MySQL Database?

10 Answers10

Linked

Related