Replace decimal(12,4) with decimal(9,3) and decimal(13,3)

[apiuser]

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.

[apiuser]

Comment from ilol, posted on GitHub Aug 20, 2014

@colinmollenhour thanks. I put it into our development queue

[apiuser]

Comment from tzyganu, posted on GitHub Aug 20, 2014

@colinmollenhour @ilol I'm butting in with something. I cannot challenge the statement "according to the ISO list of currencies there are no currencies which use 4 decimal places" but I can tell you that at least for RON (Romanian currency) for the exchange rates to EUR, USD, GBP (and maybe others) 4 decimals are taken into consideration. For example, at the time I'm writing this, the official exchange rate is 1 EUR = 4.4244 RON

[apiuser]

Comment from ilol, posted on GitHub Aug 20, 2014

So... (13,3) or (12,4)? IRR exchange rate 1 IRR = 0,0000283 EUR

[apiuser]

Comment from colinmollenhour, posted on GitHub Aug 21, 2014

So it looks like we have uncovered a separate issue.. Currently the *_to_*_rate columns are 12,4, but it sounds like they should instead be 13,7 to accommodate a wider range of exchange rates. If this is just now coming up then obviously this is an edge case, but maximizing the support for whack exchange rates with the same number of bytes sounds like a win-win to me. :+1:

I am not experienced with multi-currency so perhaps the exchange rate being >3 decimals is a valid reason for using >3 throughout, but are not the final amounts rounded anyway? E.g. if my total is 1 EUR and I purchase in RON, will I actually be invoiced for 4.4244 RON or 4.424 RON? I am questioning if all of the totals really need >3. AFAIK my credit card company has never billed me for a fraction of a cent before, but maybe that differs in other countries?

Regardless, if a case can be made for 4 decimals then 13,4 would make the most sense (13 consumes the same storage as 12). 13,4 means 13 total digits, 4 of which are decimal places.

EDIT: I understand rounding issues during calculations and I am not talking about rounding to 3 decimal places for mathematical operations, just for storage.

[apiuser]

Comment from tzyganu, posted on GitHub Aug 21, 2014

@colinmollenhour I feel that my explanation was not complete. So here are the details.
I will use the same example for RON.
Even if the exchange rates use 4 decimal places, the prices always have 2 decimals.
So unless you sell EUROs or USDs on a website, 4 decimal places will not be needed for storing prices.
But I checked around and I found one currency that use 4 decimal places. COU (or UVR) used in Columbia: http://en.wikipedia.org/wiki/ISO_4217
The same page says that there are 7 currencies that use 3 decimal places.
So I guess the 4 decimals should stay in order to support every currency.

And here is an idea to avoid rounding issues. Usually the shop owner knows what currencies he will be using. How about asking if they are going to use 3 or 4 decimal currencies when installing Magento? And then construct the decimal columns based on that selection?
I'm writing this very early in the morning and I admit I didn't think it through. But maybe someone can take this idea and turn it into something useful, or destroy it completely (I won't mind).

[apiuser]

Comment from ilol, posted on GitHub Aug 21, 2014

@tzyganu, intelligible explanation. It make sense.

[apiuser]

Comment from colinmollenhour, posted on GitHub Aug 21, 2014

Bah, I missed the UVR currency..

I do think that a dynamic solution would be nice. E.g. allow the user to select for the following:

• Amounts
• Exchange Rates
• Aggregation

I personally would then choose 9,2 - 9,5 and 13,2 but someone else may choose 13,4 - 13,7 and 16,4 for example.

[apiuser]

Comment from tzyganu, posted on GitHub Aug 22, 2014

I just found a flaw in my "unfinished idea".
If the user selects to have the decimal values stored as 9,2 because that's all he needs for prices, then all decimal attribute values are going to be stored as 9,2 because Magento stores for products all the decimal values in the same table. And the user may need 4 decimals for other attribute values. I guess if this approach is taken into consideration, there is a need for a catalog_product_entity_price table that would store all the attribute values for price type attributes.

Even if the dynamic solution for decimal columns is not considered, I still think that a separate table for price attribute values only is needed. Could make the indexing a bit faster. Should I open an other issue for this? or is it OK if I leave it here?

[apiuser]

Comment from FiveDigital, posted on GitHub Aug 22, 2014

When the decimals are changed from 4 to 3 oder 2 digits I think the rounding will be affected, too. Magento had decent rounding problems in the past and right now it looks like they're solved. Changing the decimal storage might bring back all the pain we had in the last years with PayPal, taxes, the missing cent and order export to backend systems.

[apiuser]

Comment from colinmollenhour, posted on GitHub Aug 22, 2014

@tzyganu That is a good point. However, in real world I don't think there is much use for decimal types other than monetary amounts. Text fields can store as many decimals as one needs. In one store I manage with 140 custom attributes, none of them are decimal type.

@FiveDigital So don't improve it because there might be regressions? Isn't that what the exhaustive unit tests are for?