The megabyte figures are calculated by dividing the total number of bytes by 1024 twice; the first division yields the number of kilobytes, while the second gives us the number of megabytes. Finally, that figure is passed through the round() function to limit the number of decimal places to four.
A Better Query
There are a few aspects of the above query that could be improved upon. The first is that you may want to filter out system tables. That is easily achieved using a NOT IN() clause:
WHERE table_schema not in ('information_schema', 'performance_schema', 'mysql', 'sys')
Another improvement might be to format sizes by the unit closest to their sizes, i.e. bytes, KBs, MBs, etc. The following user function accepts a FLOAT value and returns a formatted VARCHAR of 20 characters:
CREATE DEFINER=`root`@`localhost` FUNCTION `format_filesize`(filesize FLOAT) RETURNS varchar(20) CHARSET utf8 BEGIN SET @log = IFNULL(TRUNCATE(LOG(1024, filesize), 0),0); return CONCAT(ROUND(filesize / POW(1024, @log), 2), ' ', ELT(@log + 1, 'Bytes', 'KB', 'MB', 'GB', 'TB', 'PB', 'EB', 'ZB', 'YB', 'BB')); END
Here is the updated query with both modifications, as well as sorting by size in descending order:
SELECT table_schema as 'Database Name', sum( data_length + index_length ) as 'Size in Bytes', round((sum(data_length + index_length) / 1024 / 1024), 4) as 'Formatted' FROM information_schema.TABLES WHERE table_schema not in ('information_schema', 'performance_schema', 'mysql', 'sys') GROUP BY table_schema ORDER BY ByteSize DESC;
The ibdata1 File
If you use the InnoDB storage engine for most of your tables, another approach is to locate the ibdata1 file, which holds almost all the InnoDB data for a MySQL installation. Its location will vary depending on your operating system: in Debian/Ubuntu it’s under “/var/lib/mysql”, while in Windows it’s in the “data” folder under your MySQL installation directory.
Once you’ve found it, check its size. I think you’ll find that it’s fairly close to the results of the first query above.
Check the ibdata1 file size
In Linux, the ls –lh command will give you the file size:
rob@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
Listing Database and Table Sizes
Here’s a query that does double duty. Not only does it provide valuable information about each table – number of rows, data length, index length, and size - but it also includes summary information in the last row, including the database size in the bottom-right cell.
The totals are appended to the result set using the UNION statement. The query that follows employs our format_filesize() function to format all of the totals:
SELECT table_name, table_rows, data_length, index_length, round(((data_length + index_length) / 1024 / 1024),2) "Size" FROM information_schema.TABLES WHERE table_schema = "sakila" UNION SELECT 'TOTALS:', format_filesize( sum(table_rows) ), format_filesize( sum(data_length) ), format_filesize( sum(index_length) ), format_filesize( sum(data_length + index_length) ) FROM information_schema.TABLES WHERE table_schema = "sakila";
Determining Column Sizes
You might think that you could just take the number of rows and multiply it by a field’s data type size to determine how many bytes it takes up on disk. For instance, if a column’s type is numeric, you could just get a count on the rows, and multiply it by the size, such as 4 for INTEGER, or 8 for DOUBLE, etc…
Sadly, that won't reflect the actual size taken up on disk, because of many factors, including the storage engine type, row format, fragmentation, and a whole host of other factors. Thus, estimating a column’s storage requirements is difficult, though certainly not impossible, to get at.
That being said, if you’re just looking for a general estimation, you can always use the OCTET_LENGTH() function. A synonym for LENGTH(), OCTET_LENGTH() returns the length of a field in bytes. A multi-byte string character counts as multiple bytes, so for a string containing five 2-byte characters, OCTET_LENGTH() returns 10, whereas CHAR_LENGTH() would return 5.
With that in mind, here is a query that measures the size of two fields of the sakila film table:
select title, octet_length(title) 'title_length', length 'movie_length', octet_length(length) 'movie_length_length' from film limit 10;