MySQL Forums
Forum List  »  Optimizer & Parser

Strategy needed for picking random rows
Posted by: Joachim Berger
Date: September 07, 2011 11:27AM

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!

Options: ReplyQuote


Subject
Views
Written By
Posted
Strategy needed for picking random rows
3148
September 07, 2011 11:27AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.