DECIMAL vs INT what is the best optimized choice?

Question

I'm sure this ask in incomplete without a precise context, so I'll try to explain it, and I'll try to be clear
I need to store a whole of data rapresented on the page with grams, milligrams, micrograms and kilojoule.

All of this data is like 99999.99g (kilojoule apart), so I could use DECIMAL(7,2) or DECIMAL(5,2) with older MySql versions.

However, I've saw in mysql site, DECIMAL datatype is more heavy than a MEDIUMINT (am I wrong?) that could be enough to store the data.
Mainly, I'll do numeric operations on decimals or floats from the user machine, and don't need to store them again, so I'm doubtful about what is the best datatype in this case.

so what is the best datatype in this case?

Answer 1

One purpose of databases is to provide and ensure data integrity, and to store data in a format that is as close to the natural format of the type of data that needs to be stored. With that in mind, you should not attempt to optimize data types for the database engine prematurely (unless you've done benchmarks and you have a very good reason). Instead, your efforts are better spent on designing your table structures appropriately with the correct relationships and creating indexes where appropriate to help the database engine deal with the volume and types of data that you have. You will be surprised how much data a DB engine can process if the data is normalized properly and indexes and relationships are created where appropriate.

So, as a best practice, prefer the DECIMAL type for storing decimal or fractional numbers such as prices, fractional quantities etc.

Answer 2

Store the data as it is with as much precision as you can. You can always truncate it when you select it.

You may regret not storing the extra data a year from now when you need the precision.

Answer 3

As best I understand it, DECIMAL is for exact fixed point arithmetic, which is a relatively esoteric branch of numerical computing. It gets used when you work with numbers with an integer part and a fractional part but when the errors intrinsic to floating point arithmetic can't be allowed (e.g. accounting).

So I'd guess that if you don't already know that you need to use DECIMAL, you probably don't.

Answer 4

Given that you need accuracy, keep using the DECIMAL, and don't use the float types. DECIMAL will do the conversions that you'd have to if you were using MEDIUM int.

Try reading this sometime for why: http://docs.sun.com/source/806-3568/ncg%5Fgoldberg.html