MySQL database size estimation

Question

I have an application database with a table for users (1kbyte of data per user based on counting fields * typelength), and about 100 things of the same size belonging to a user (0.5 kbyte per thing), and it is in a "user" table and a "thing" table.

That would seem to lead to about 51kbytes of data per user. However, I have heard that for MySQL, I should double it to cover index tables, which would get me to 102kbytes/user Is that true? Are there any other data expansion factors to consider for MySQL, or is 102 kbytes a good estimate?

Besides the indexing factor (which I think is 2) and the storage efficiency (which I also think is 2), are there any other multipliers for data storage in MySQL?

Answer 1

Short answer
Size increase 2-3x over MyISAM is common, 4x is rare.

Everything about the InnODB engine:
http://dev.mysql.com/doc/refman/5.1/en/innodb-storage-engine.html

InnoDB, MyISAM and disk space:
http://mysqlha.blogspot.com/2009/01/innodb-myisam-and-disk-space_16.html

MySQL engines space usage comparison:
Part1: http://marksverbiage.blogspot.com/2008/02/mysql-engines-and-space-usage.html
Part2: http://marksverbiage.blogspot.com/2008/04/mysql-engines-space-usage-comparison.html

Here's the physical row structure:
http://dev.mysql.com/doc/refman/5.0/en/innodb-physical-record.html

There are a lot of variables and issues:

• Indexes, remember InnoDB includes the PK in every secondary index.
• Are you packing keys (slow)?
• Is the table redundant?
• Don't forget about the logs (binary log, slow query log, error log ....)
• Are rows declared as nullable, if so add an extra byte per nullable column per row.
• What charset are you using?