choose nearest value

[greatstar00]

I have a table which holds the integer values(the value representing some data) of many users

the table look like this (MySQL)

id  f1   f2    f3

1   30  50   15

2   31  17   37

3   18  31   54

and more ....

 

 

 

i randomly select a user from the another table(let's call it sel_user), and then i select a user who got the numbers closest to the sel_user or same as the sel_user, from the table above

 

 

here is the method of select closest user,

it is based on the sum of 3 columns, f1, f2, f3, (i.e user 2, so the numbers are 31  17 37)

if a sel_user got values 12  30 18, then the sum is 60

from the table above, user 2 is closest, because 31+17+37 is 85, others are larger than 85

 

 

 

my solution is (with mysql query)

add sum the user's value, put it onto the stack for all users, then compare, abs(sel_user's sum - user_from_table's sum)

 

 

 

 

my question is

is there any faster way to do this?

the method i used gets slower and slower, because the mysql string i sent is longer and longer based on numbers of users

or is there a way to make the mysql string always has same length each time?

[Orio]

You can add another column, called sum, that will sum f1,f2,f3 and will be updated whenever you change the values of f1,f2,f3.

This way you will only have to check which value is the closest to the values in "sum", and not to start summing each row every time.

 

This will probably make your scripts run faster

 

Orio.

[sasa]

SELECT id FROM users ORDER BY ABS(f1 + f2 + f3 - 60) LIMIT 1

[greatstar00]

thx alot people, this really helped