Geek Answers Handbook

Is DECIMAL vs INT the best optimized choice?

I am sure that this request is incomplete without an exact context, so I will try to explain this and I will try to be clear
I need to store all the information presented on the page, grams, milligrams, micrograms and kilojoules.

All of this data is similar to 99999.99g (in kilojoules), so I could use DECIMAL (7.2) or DECIMAL (5.2) with older versions of MySql.

However, I saw on the mysql site that the DECIMAL data type is heavier than MEDIUMINT (am I mistaken?), Which may be enough to store data.
Basically, I will do numerical operations with decimals or float from the user machine, and do not need to store them again, so I doubt that in this case the best data type.

and what is the best data type in this case?

+4
source share
4 answers

One of the goals of databases is to ensure and ensure data integrity and save data in a format that is as close as possible to the natural format of the data type that needs to be saved. With this in mind, you should not try to optimize the data types for the database engine prematurely (unless you have done the tests and you have a very good reason). Instead, your efforts are better spent on properly designing table structures with the right relationships and creating indexes where necessary to help the database engine deal with the amount and types of data that you have. You will be surprised how much data the database can process if the data is normalized properly, and where necessary, indexes and relationships are created.

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

+11
source

Store data the same way as with maximum accuracy. You can always truncate it when you select it.

You may regret not storing additional data after a year when you need accuracy.

+1
source

As far as I understand, DECIMAL is for exact fixed-point arithmetic , which is a relatively esoteric branch of numerical calculations. It is used when you are working with numbers with an integer part and a fractional part, but when errors inherent in floating point arithmetic cannot be resolved (for example, accounting).

So, I assume that if you still do not know that you need to use DECIMAL, you probably do not.

0
source

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

Try to read this someday why: http://docs.sun.com/source/806-3568/ncg_goldberg.html

0
source

More articles:


All Articles