Take the 2-minute tour ×
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. It's 100% free, no registration required.

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?

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 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

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.