MySQL: Select Random Entry, but Weight Towards Certain Entries

This guy asks the same question. He says the same as Frank, but the weightings don't come out right and in the comments someone suggests using ORDER BY -LOG(1.0 - RAND()) / Multiplier, which in my testing gave pretty much perfect results.

(If any mathematicians out there want to explain why this is correct, please enlighten me! But it works.)

The disadvantage would be that you couldn't set the weighting to 0 to temporarily disable an option, as you would end up dividing by zero. But you could always filter it out with a WHERE Multiplier > 0.

For a much better performance (specially on big tables), first index the weight column and use this query:

SELECT * FROM tbl AS t1 JOIN (SELECT id FROM tbl ORDER BY -LOG(1-RAND())/weight LIMIT 10) AS t2 ON t1.id = t2.id

On 40MB table the usual query takes 1s on my i7 machine and this one takes 0.04s.

For explanation of why this is faster see MySQL select 10 random rows from 600K rows fast