Feature request from colinmollenhour, posted on GitHub Aug 19, 2014
MySQL decimal storage uses a minimum of 4 byes and can store 9 digits in these 4 bytes. Going up to 6 bytes you can store 13 digits.
In most cases decimal(9,3) is sufficient and it would save 2 bytes per row/column. The maximum value would be 999,999.999 (1 million) instead of 99,999,999.9999 (100 million). Unless you are selling original artwork in Iranian Rial this should be sufficient. Also according to the ISO list of currencies there are no currencies which use 4 decimal places (but plenty that use 3, so 2 is not acceptable).
For aggregation tables the better storage type would be decimal (13,3) since still 4 decimals are not needed and 13 takes the same storage space as 12. So for the same storage you can store numbers 100 times larger.
It may seem like a minor optimization but in Magento 1 my sales_ tables currently have over 500 decimal(12,4) columns (some are third-party). 2 bytes 500 columns * 1 million rows is almost 1GB of storage, not to mention the indexed columns. Not huge, but quite significant.
While you're at it, the report_event_types.event_type_id and report_event.event_type_id and report_event.subtype should be tinyint instead of smallint. Maybe these were removed or already changed in M2.. There are probably other columns that could be optimized as well. I think there is even a strong case to be made for changing store_id to "tinyint unsigned" throughout since operating over 256 stores is an edge case.
I realize the DBMS abstraction may be an impediment to this change but considering only MySQL is actually supported I think that is far less important than a database that isn't wasteful of resources.
Lastly, for anyone who is an edge case and decimal(9,3) is not sufficient it should be quite easy to install Magento, dump the database, use sed or a text editor to do a simple replacement and then re-import.
... View more