How to Check the Size of a Database in MySQL

In MySQL, you can query the information_schema.tables table to return information about the tables in a database. This table includes information about the data length, index length, as well as other details such as collation, creation time, etc. You can use the information in this table to find the size of a given database or all databases on the server.

You can also use the MySQL Workbench GUI to find details about the database (including its size).
This article provides a quick overview of both methods.

Code Example

Here’s an example of finding the size of each database by running a query against the information_schema.tables table:

SELECT 
    table_schema 'Database Name',
    SUM(data_length + index_length) 'Size in Bytes',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) 'Size in MiB'
FROM information_schema.tables 
GROUP BY table_schema;

Result:

+--------------------+---------------+-------------+
| Database Name      | Size in Bytes | Size in MiB |
+--------------------+---------------+-------------+
| information_schema |             0 |        0.00 |
| Music              |         98304 |        0.09 |
| mysql              |       2506752 |        2.39 |
| performance_schema |             0 |        0.00 |
| sakila             |       6766592 |        6.45 |
| Solutions          |         16384 |        0.02 |
| sys                |         16384 |        0.02 |
| world              |        802816 |        0.77 |
+--------------------+---------------+-------------+

In this example I’ve listed the size in bytes and in mebibytes (MiB), but you can choose how you want to present it.
Of course, you can always narrow it down to a specific database if you need to. Simply add a WHERE clause with the name of the database:

SELECT 
    table_schema 'Database Name',
    SUM(data_length + index_length) 'Size in Bytes',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) 'Size in MiB'
FROM information_schema.tables 
WHERE table_schema = 'sakila';

Result:

+---------------+---------------+-------------+
| Database Name | Size in Bytes | Size in MiB |
+---------------+---------------+-------------+
| sakila        |       6766592 |        6.45 |
+---------------+---------------+-------------+

The FORMAT_BYTES() Function

You can use the sys.FORMAT_BYTES() function to save yourself converting the size into mebibytes, kibibytes, or whatever. This function takes a value, converts it to human-readable format and returns a string consisting of a value and a units indicator. The converted value will depend on the size of the value (so the result could be in bytes, KiB (kibibytes), MiB (mebibytes), GiB (gibibytes), TiB (tebibytes), or PiB (pebibytes).
Here’s an example of rewriting the previous example to use the FORMAT_BYTES() function:

USE Music;
SELECT 
    table_schema 'Database Name',
    SUM(data_length + index_length) 'Size in Bytes',
    sys.FORMAT_BYTES(SUM(data_length + index_length)) 'Size (Formatted)'
FROM information_schema.tables 
GROUP BY table_schema;

Result:

+--------------------+---------------+------------------+
| Database Name      | Size in Bytes | Size (Formatted) |
+--------------------+---------------+------------------+
| information_schema |             0 | 0 bytes          |
| Music              |         98304 | 96.00 KiB        |
| mysql              |       2506752 | 2.39 MiB         |
| performance_schema |             0 | 0 bytes          |
| sakila             |       6766592 | 6.45 MiB         |
| Solutions          |         16384 | 16.00 KiB        |
| sys                |         16384 | 16.00 KiB        |
| world              |        802816 | 784.00 KiB       |
+--------------------+---------------+------------------+

MySQL Workbench

Another way of finding the database size is to use the MySQL Workbench GUI. Here’s how:

1. Navigate to the database in the Schemas pane
2. Hover over the applicable database
3. Click the little information icon beside the database name. This loads information about the database, including its approximate size, table count, collation, etc. The database size is listed on the Info tab (usually the default tab).

How to Get True Size of MySQL Database?

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?

 Answers

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; 

---

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:

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

for your purposes.

---

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/

---

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

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 ;


Copy

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


Copy

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

Using the GitHub API and PHP to Get Repository Information

GitHub is an awesome place to host your open source project code. MooTools, Prototype, and jQuery all use GitHub. As you probably know, the MooTools Forge requires your plugins be hosted on GitHub. The only problem with hosting all my MooTools plugins is that I lose traffic when I want people to see my code. Problem solved: use PHP, the GitHub API, and PHP Markdown to display files of my choice on my website.


Our goals with this code will be to:

Connect to GitHub via the API to retrieve repository information.
Retrieve the content of two files from the repository: a source file and the README.md Markdown file.
Cache the information for a given period of time to reduce the load on GitHub.
Use PHP Markdown to output a formatted README.md file.
I know that seems like a lot of work but you'll be amazed at how easy the process is.

PHP MarkDown
You may download PHP Markdown at Michel Fortin's website. It's simple and full of features.

The PHP
The first step is to build a PHP function that will connect to GitHub using cURL:

/* gets url */
function get_content_from_github($url) {
$ch = curl_init();
curl_setopt($ch,CURLOPT_URL,$url);
curl_setopt($ch,CURLOPT_RETURNTRANSFER,1);
curl_setopt($ch,CURLOPT_CONNECTTIMEOUT,1);
$content = curl_exec($ch);
curl_close($ch);
return $content;
}
Next we need to define a few settings:

/* static settings */
$plugin = 'Overlay';
$cache_path = $_SERVER['DOCUMENT_ROOT'].'/plugin-cache/';
$cache_file = $plugin.'-github.txt';
$github_json = get_repo_json($cache_path.$cache_file,$plugin);
The next step is to create another PHP function that grabs the repository information (JSON-encoded, because I love JSON) -- either fresh from GitHub (by first grabbing the most recent commit hash, then grabbing the contents of the two files) or our local cached information:

/* gets the contents of a file if it exists, otherwise grabs and caches */
function get_repo_json($file,$plugin) {
//vars
$current_time = time(); $expire_time = 24 * 60 * 60; $file_time = filemtime($file);
//decisions, decisions
if(file_exists($file) && ($current_time - $expire_time < $file_time)) {
//echo 'returning from cached file';
return json_decode(file_get_contents($file));
}
else {
$json = array();
$json['repo'] = json_decode(get_content_from_github('http://github.com/api/v2/json/repos/show/darkwing/'.$plugin),true);
$json['commit'] = json_decode(get_content_from_github('http://github.com/api/v2/json/commits/list/darkwing/'.$plugin.'/master'),true);
$json['readme'] = json_decode(get_content_from_github('http://github.com/api/v2/json/blob/show/darkwing/'.$plugin.'/'.$json['commit']['commits'][0]['parents'][0]['id'].'/Docs/'.$plugin.'.md'),true);
$json['js'] = json_decode(get_content_from_github('http://github.com/api/v2/json/blob/show/darkwing/'.$plugin.'/'.$json['commit']['commits'][0]['parents'][0]['id'].'/Source/'.$plugin.'.js'),true);
file_put_contents($file,json_encode($json));
return $content;
}
}
Once we've acquired the appropriate information, we output the information to screen:

/* build json */
if($github_json) {

//get markdown
include($_SERVER['DOCUMENT_ROOT'].'/wp-content/themes/walshbook3/PHP-Markdown-Extra-1.2.4/markdown.php');

//build content
$content = '<p>'.$github_json['repo']['repository']['description'].'</p>';
$content.= '<h2>MooTools JavaScript Class</h2><pre class="js">'.$github_json['js']['blob']['data'].'</pre><br />';
$content.= trim(str_replace(array('<code>','</code>'),'',Markdown($github_json['readme']['blob']['data'])));
}
That's all! Now I get the benefit of hosting my code on GitHub but displaying it on my own website. I've created a special WordPress template page to do so and recommend you do too!

MySQL: Get the exact size of a database by SQL query

phpMyAdmin doesn’t show the exact size in MB when the size of your database exceeds 1GB. It just shows something like 4.2GB, truncating everything out in the 100MB precision. So is it possible to get the database size in MB in exact numbers using MySQL query?

Yes, it is:

SELECT CONCAT(sum(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2))," MB") AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA like 'YOUR_DATABASE_NAME_HERE%' ;

Just change YOUR_DATABASE_NAME_HERE into the name of your database. And it would prints out something like this:

+------------+
| Size       |
+------------+
| 4906.79 MB |
+------------+
1 row in set (0.05 sec)

You can also get the size of a specific table. Read here: http://www.novell.com/communities/node/8706/check-mysql-database-size-using-sql-query