Re: Strategy needed for picking random rows

[Rick James]

I'm glad to see you have figured out the inner-outer query trick. Almost. The LIMIT needs to be in the subquery. The index does need to include `id`. (If it is the PRIMARY KEY on InnoDB, it will be implicitly part of the index.)

See if this runs faster:

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')
            ORDER BY  RAND()
            LIMIT  200
      ) x USING (id) ;

Alas, that does not take care of haircolor.

Something to try, for the more general case:
Add an extra FLOAT column `rnd`; populate it with RAND().
You may need to repopulate that column periodically, else the following query will tend to clump the same people together often.
Have INDEX(rnd, id)
Then do this:

SELECT  ...
    FROM  members m
    JOIN  
      ( SELECT  id
            FROM  members
            WHERE  rnd > RAND()   -- **
              AND  sex = 'm'
              AND  ...
            ORDER BY  rnd         -- **
            LIMIT  200            -- **
      ) USING (id);

However, if RAND() is too big, it will run out of rows before it gets 200. For that case, I suggest keeping the rows you did get, then grabbing the rest of the 200 via a second SELECT that is missing "rnd > RAND()" and has a lower LIMIT. This will "wrap" around.