For e.g. a query like this:
SELECT cid from category where [...] order by rand()*weight DESC LIMIT 1
is not using indices as shown by EXPLAIN statements. Obviously this overloads the server.
My question is that what can be done to select a weighted-random row from a MySQL table more efficiently? Has this been improved in MySQL5?
How can MySQL be forced to use indices for queries like this where we have weighted rand() function involved?
Any pointers are appreciated.