Database size calculation?

mysql

Solution

If you want to know the current size of a database you can try this:

SELECT table_schema "Database Name"
     , SUM(data_length + index_length) / (1024 * 1024) "Database Size in MB"
FROM information_schema.TABLES
GROUP BY table_schema

Problem

What is the most accurate way to estimate how big a database would be with the following characteristics: - MySQL - 1 Table with three columns: - id --> big int) - field1 --> varchar 32 - field2 --> char 32 - there is an index on field2 You can assume varchar 32 is fully populated (all 32 characters). How big would it be if each field is populated and there are: - 1 Million rows - 5 Million rows - 1 Billion rows - 5 Billion rows My rough estimate works out to: 1 byte for id, 32 bits each for the other two fields. Making it roughly: ``` 1 + 32 + 32 = 65 * 1 000 000 = 65 million bytes for 1 million rows = 62 Megabyte ``` Therefore: - 62 Mb - 310 Mb - 310 000 Mb = +- 302Gb - 1 550 000 Mb = 1513 Gb Is this an accurate estimation?

Original source