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:
mysql> use test Database changed mysql> create table ftest(f float); Query OK, 0 rows affected (0.06 sec)
mysql> insert into ftest values (6.984789027653891),(39.484789039053891); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from ftest; +---------+ | f | +---------+ | 6.98479 | | 39.4848 | +---------+ 2 rows in set (0.06 sec)
mysql> select round(f,15) from ftest; +--------------------+ | round(f,15) | +--------------------+ | 6.984788894653320 | | 39.484790802001953 | +--------------------+ 2 rows in set (0.01 sec)
Note that the values selected are not equal to the values inserted. What values you get will depend on the underlaying c library used by mysql, which again is plattform dependant.
-- Roger
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
