# Seeding Pivot table with percentage

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`
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 `userGames`?

-
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 u.id, g.id 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: pastebin.com/zvh6dY1G - 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