Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Given two tables - users and games - and a pivot table - userGames, I'm trying to efficiently seed the userGames table For testing, so that each user has close to a set percentage of games.

The query I've come up with so far relies on cross joining users with games, adding a RAND() value for each row, and then selecting the rows where the random column is below a given threshold:

SET @percent := 30;

INSERT IGNORE INTO `userGames`(`id_user`,`id_game`)
SELECT `user`, `game`
        `u`.`id` AS `user`,
        `g`.`id` AS `game`,
        RAND() AS `rand`
    FROM `users` AS `u`
    CROSS JOIN `games` AS `g`
    HAVING `rand` < (@percent/100)
) AS `NEW`

The problem with this is that for a very high number of users (~500k) and games (~100) this can take a long time, I'm assuming mostly due to having to calculate RAND() for each row.

Is there a better way of randomly seeding a pivot table like this with a given percentage?

Also just out of curiosity, can this be done without the subquery, without also having to insert the rand field into userGames?

share|improve this question
Inserting 15M rows will take some time anyway. Are you sure the bottleneck is the RAND() calls and not the IO speed? – ypercubeᵀᴹ Oct 18 '13 at 16:19
You don't need the subquery: INSERT ... SELECT, FROM users AS u CROSS JOIN games AS g WHERE RAND() < (@precent/100); – ypercubeᵀᴹ Oct 18 '13 at 16:22
Hmm, your suggested query returns the square of the percentage: - probably because it's operating on the join (I get the same result if I use ON RAND()... instead of WHERE). Any way you could revise it so that it actually returns the expected count? – Johannes Oct 20 '13 at 14:51
(woops, in the pastebin the 25% values are wrong) To clarify, it seems that the percentage in your query affects if the user is cross-joined on the games, meaning they either get ALL the games or NONE at all. However you're right, it's probably IO speed and not RAND() speed that's the bottleneck here. – Johannes Oct 20 '13 at 15:01

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.