Hi,
Working on a db driven loot system, I need to build a fancy query and I'm not sure about the best approach to use. The system itself is rather simple, randomizing over weighted records of category tables with :
SELECT resref FROM table ORDER BY rand()*rarity ASC LIMIT 1
I know, using ORDER BY RAND() is unadvised, but my tables hold 200-500 records, hopefully the speed will not be affected too much, yet if you know a better approach I'm all ears of course.
So, the problem is, I have one table(categories) holding all the available loot categories, each one being indexed by a bitwise value(fortunately I have fewer than 32 categories). Each category on this table is weighted and should refer to another table holding the items of the category. So I need the query to randomize on the categories table, then randomize on the (items) table returned by the first operation, yielding a random item resref. 2 queries would work, but can a single, faster query do it?
Thanks for your time
Kato
