But few compare actual storage space usage. As this is very important to our application, I decided to run some tests.

I'm testing here with a realistic-sized table for our application (we partition data into daily partitions and spread them across many servers anyway, so this is just a small piece). We currently use MyISAM, and this is a typical table with approximately 4 million rows.

I can't dump the schema or content of this table here for confidentiality reasons, but it has:

47 columns, many of which are VARCHARs

17 indexes

Typical row size of 300 bytes (as reported by "SHOW TABLE STATUS")

Method

First I generated 4 Million rows of dummy data using a test tool (this is extremely useful as it avoids having to take real, potentially sensitive and very large data from production). Then I measured the space usage using SHOW TABLE STATUS, and successively ALTERed the table to different engines.

Note that ALTERing a table causes its indexes to be rebuilt - they will be a good deal smaller than if the rows had been inserted normally. Therefore, just an ALTER which doesn't change anything could make a big space saving.

Results

Engine

Data size

Index size

Notes

MyISAM, PACK_KEYS=1 (initial)

1152854640

682459136

Initial data load

MyISAM, PACK_KEYS=0

1152854640

2396920832

ALTER

MyISAM, PACK_KEYS=DEFAULT

1152854640

677806080

ALTER

MyISAM, PACK_KEYS=1 (alter)

1152854640

433162240

ALTER

InnoDB

1973420032

2442166272

ALTER

sizes are in bytes.

In case that's not clear from the numbers above, here is a summary of the results