MySQL Forums :: Optimizer & Parser :: Strategy needed for picking random rows


Advanced Search

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 2344 Joachim Berger 09/07/2011 11:27AM
Re: Strategy needed for picking random rows 975 Øystein Grøvlen 09/08/2011 01:08AM
Re: Strategy needed for picking random rows 810 Joachim Berger 09/08/2011 01:17AM
Re: Strategy needed for picking random rows 759 Rick James 09/08/2011 07:51AM
Re: Strategy needed for picking random rows 819 Joachim Berger 09/08/2011 08:05AM
Re: Strategy needed for picking random rows 774 Rick James 09/09/2011 08:51AM


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.