434,720 Members | 2,157 Online
Need help? Post your question and get tips & solutions from a community of 434,720 IT Pros & Developers. It's quick & easy.

# Choosing a random entry in a table by "weight"

 P: n/a I have a MySQL table of servers, I use RAND() to pick a random server to use each time, but how can I add a number to each server entry that allows it be to picked more often than the other 20 servers? For example Server1's weight is 80 and Server2's weight is 40 and hence Server1 is more likely to be picked than the others. May 27 '07 #1
11 Replies

 P: n/a al*********@googlemail.com wrote: I have a MySQL table of servers, I use RAND() to pick a random server to use each time, but how can I add a number to each server entry that allows it be to picked more often than the other 20 servers? For example Server1's weight is 80 and Server2's weight is 40 and hence Server1 is more likely to be picked than the others. I dont think myqsl can do that (corect me if im wrong please) If the weights aren't too large i would solve it something like this: Offcourse this will get verry ineffective if a=1,b=100000000, etc link->weight a->1 b->3 c->2 ->select all links ->create a new array \$w=(a,b,b,b,c,c) and voilla \$link=\$w[rand(1,count(\$w))]; -- Arjen http://www.hondenpage.com - mijn site over honden May 27 '07 #2

 P: n/a Floortje wrote: Whoops \$link=\$w[rand(0,(count(\$w)-1))]; May 27 '07 #3

 P: n/a al*********@googlemail.com kirjoitti: I have a MySQL table of servers, I use RAND() to pick a random server to use each time, but how can I add a number to each server entry that allows it be to picked more often than the other 20 servers? For example Server1's weight is 80 and Server2's weight is 40 and hence Server1 is more likely to be picked than the others. In theory like this: pick a random weight and take into group all the items that weigh more, it's more likely that heavier items are taken into the group, than the lighter. From the group you have created, pick one randomly. If no items were in the group, your random weight was too big, so you might limit it to < maxweight, so that always at least one item is picked. Transforming this into a query is another thing. This is very rough example but you'll get the idea, I suppose: SELECT * FROM ( SELECT * FROM servers WHERE weight RAND() ) ORDER BY RAND() LIMIT 1,1 -- Ra*********@gmail.com "Wikipedia on vähän niinq internetin raamattu, kukaan ei pohjimmiltaan usko siihen ja kukaan ei tiedä mikä pitää paikkansa." -- z00ze May 27 '07 #4

 P: n/a On May 27, 12:10 pm, alpha.be...@googlemail.com wrote: I have a MySQL table of servers, I use RAND() to pick a random server to use each time, but how can I add a number to each server entry that allows it be to picked more often than the other 20 servers? For example Server1's weight is 80 and Server2's weight is 40 and hence Server1 is more likely to be picked than the others. You could also do something like: select serverId from server order by rand() / weight limit 1 May 28 '07 #5

 P: n/a Rami Elomaa wrote: al*********@googlemail.com kirjoitti: >I have a MySQL table of servers, I use RAND() to pick a random serverto use each time, but how can I add a number to each server entry thatallows it be to picked more often than the other 20 servers?For example Server1's weight is 80 and Server2's weight is 40 andhence Server1 is more likely to be picked than the others. In theory like this: pick a random weight and take into group all the items that weigh more, it's more likely that heavier items are taken into the group, than the lighter. From the group you have created, pick one randomly. If no items were in the group, your random weight was too big, so you might limit it to < maxweight, so that always at least one item is picked. Transforming this into a query is another thing. This is very rough example but you'll get the idea, I suppose: SELECT * FROM ( SELECT * FROM servers WHERE weight RAND() ) ORDER BY RAND() LIMIT 1,1 Nice .. real nice but wrong :-( say you have this a-2 b-1 c-1 d-1 Valid random weight numbers are 1 and 2 (both 50%) Then your chances of picking A are 62.5% while B,C,D only get a 12.5% change of being picked (instead of 40-20-20-20) -- Arjen www.hondenpage.com May 28 '07 #6

 P: n/a Floortje kirjoitti: Rami Elomaa wrote: >al*********@googlemail.com kirjoitti: >>I have a MySQL table of servers, I use RAND() to pick a random serverto use each time, but how can I add a number to each server entry thatallows it be to picked more often than the other 20 servers?For example Server1's weight is 80 and Server2's weight is 40 andhence Server1 is more likely to be picked than the others. In theory like this: pick a random weight and take into group all theitems that weigh more, it's more likely that heavier items are takeninto the group, than the lighter. From the group you have created,pick one randomly. If no items were in the group, your random weightwas too big, so you might limit it to < maxweight, so that always atleast one item is picked.Transforming this into a query is another thing. This is very roughexample but you'll get the idea, I suppose:SELECT * FROM ( SELECT * FROM servers WHERE weight RAND() ) ORDER BYRAND() LIMIT 1,1 Nice .. real nice but wrong :-( I don't know what would make it particularily "wrong", it does basicly what it should, but the distribution of percentages is different. How would I know what is "wrong" and what "right", so I just say it's "different". The original question does not define exactly what is "more often than the others". -- Ra*********@gmail.com "Wikipedia on vähän niinq internetin raamattu, kukaan ei pohjimmiltaan usko siihen ja kukaan ei tiedä mikä pitää paikkansa." -- z00ze May 28 '07 #7

 P: n/a Rami Elomaa schreef: >Nice .. real nice but wrong :-( I don't know what would make it particularily "wrong", it does basicly what it should, but the distribution of percentages is different. How would I know what is "wrong" and what "right", so I just say it's "different". The original question does not define exactly what is "more often than the others". The op asked for 'weighted'. I really like your solution but it is not weighted. http://en.wikipedia.org/wiki/Weight_function -- Arjen http://www.hondenpage.com May 28 '07 #8

 P: n/a >Nice .. real nice but wrong :-( > I don't know what would make it particularily "wrong", it does basicly what it should, but the distribution of percentages is different. How would I know what is "wrong" and what "right", so I just say it's "different". The original question does not define exactly what is "more often than the others". The op asked for 'weighted'. I really like your solution but it is not weighted. http://en.wikipedia.org/wiki/Weight_function -- Arjen http://www.hondenpage.com May 28 '07 #9

 P: n/a al*********@googlemail.com wrote: I have a MySQL table of servers, I use RAND() to pick a random server to use each time, but how can I add a number to each server entry that allows it be to picked more often than the other 20 servers? For example Server1's weight is 80 and Server2's weight is 40 and hence Server1 is more likely to be picked than the others. SELECT *, weight*RAND() as result FROM Servers ORDER BY result LIMIT 1 May 29 '07 #10

 P: n/a Alexey Kulentsov wrote: SELECT *, weight*RAND() as result FROM Servers ORDER BY result LIMIT 1 .... ORDER BY result DESC ... May 29 '07 #11

 P: n/a On May 29, 12:55 pm, Alexey Kulentsov

### This discussion thread is closed

Replies have been disabled for this discussion.