Strategy needed for picking random rows
Hi again, folks,
please imagine the following query on an innodb 'members' table:
SELECT m.id, m.bulky_field1, ...
FROM members m
INNER JOIN (
SELECT id
FROM members
WHERE (sex = 'm')
AND (img = 1)
AND (home IN (1,2,3,4,5,6,7,8))
AND (dob BETWEEN '1960-09-07' AND '1979-09-08')
) x USING (id)
LIMIT 200;
We have an efficient, selective index on (sex,img,home,dob) which might return a few thousand rows or less, and we might even add some more criteria like 'haircolor' to the outer query without any problem. So far, so good.
But without any ORDER BY in the inner query, the result will be output in the order of the index. So, in this case, members with the lowest "home" value will be listet first. We'll probably even only get users having home=1, which is annoying. Plus, users running the query will probably always see the same 200 people.
My question now is: I'd like a random result every time, like ORDER BY RAND() in the inner query, which of course does not perform well.
The only promising solution so far was to think about an additional column "rnd" for each user (which would be updated application-wise or so), change the index to (sex,img,rnd,home), and then do an ORDER BY rnd in the inner query. This would work well, but then I am losing the handy selectivity of the 'dob' (birthdate). As it's a range, I can't leave it in the index.
Any ideas? Thanks!