Given two tables -
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
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` FROM ( SELECT `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