On Wed, 2005-02-02 at 12:46, Roger Baklund wrote: > Galen wrote: > > I've got a huge table going, and it's storing a load of numeric data. > > Basically, a percentage or single digit rank, one or two digits before > > the decimal and fifteen after, like this: > > > > 6.984789027653891 > > 39.484789039053891 > > > > What is the most efficient way to store these values? I will be > > frequently sorting results by them or using math with them, so speed is > > important, but I also don't want to be wasteful of disk space as I > > currently have over three quarters of a million records, with more to come. > > I think the most efficient way is to multiply all values with 100 before > you insert, and store as a bigint. Integer math operations are fast. > > You should not use FLOAT, it is an approximate type, not storing the > exact values you enter, but an approximation: > Actually, while it is true that MySQL uses the platform's C library for the type, all modern libraries use the IEEE standards for floating point.. in 'C' terms. The approximate values you might see in a casual query are the result of the default format mask used in MySQL. Modern floating point units (a subsystem of the CPU) perform basic math (addition, multiplication) with very nearly the same speed as integer types, so I would not worry about that too much..
The MySQL 'DOUBLE' type accepts scale and precision as arguments when created. from http://dev.mysql.com/doc/mysql/en/numeric-types.html The precision represents the number of significant decimal digits that will be stored for values, and the scale represents the number of digits that will be stored following the decimal point. ... The FLOAT type is used to represent approximate numeric data types. The SQL standard allows an optional specification of the precision (but not the range of the exponent) in bits following the keyword FLOAT in parentheses. The MySQL implementation also supports this optional precision specification, but the precision value is used only to determine storage size. A precision from 0 to 23 results in four-byte single-precision FLOAT column. A precision from 24 to 53 results in eight-byte double-precision DOUBLE column. -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
