For the most efficient use of storage, try to use the most precise type in all cases. For example, if an integer column is used for values in the range from 1 to 99999, MEDIUMINT UNSIGNED is the best type. Of the types that represent all the required values, it uses the least amount of storage.

For earlier MySQL versions, accurate representation of monetary values was a common problem. In these MySQL versions, you should also use the DECIMAL type. In this case the value is stored as a string, so no loss of accuracy should occur on storage. However, calculations on these DECIMAL values are done using double-precision operations. If accuracy is not too important or if speed is important, the DOUBLE type may also be good enough.

For high precision, you can always convert to a fixed-point type stored in a BIGINT. This allows you to do all calculations with 64-bit integers and then convert results back to floating-point values only when necessary.

PROCEDURE ANALYSE can be used to obtain suggestions for optimal column data types. For more information, see Section 18.3.1, “PROCEDURE ANALYSE”.