Function for generate a huge and possible random relationship between tables I have this two tables and N:M relationship between them:



Let's said I am managed to create a lot of media and emails records but I need to create the relationship between them. I can do this programmatically but I don't want to waste time on that if I can do in someway using MySQL functions or any other way. So, any advice, code example, query example?
Lets say that the media tables has 1K rows and the emails one has 100K rows.

Then this will insert into the emails_media_xref all possible combinations, 1K x 100K = 100M, that is a hundred million rows.
Warning, this may take some time:

INSERT INTO emails_media_xref
    (emails_id, media_id)
SELECT 
    emails.id, media.id 
FROM emails 
  CROSS JOIN 
     media ;


If you want to insert a row for every email, that is 100K rows (random, different choice of media per email):

INSERT INTO emails_media_xref
    (emails_id, media_id)
SELECT 
    emails.id, 
    ( SELECT media.id
      FROM media
      ORDER BY RAND()
      LIMIT 1
    ) 
FROM emails ;


If you want to insert (up to) @N rows for every email, that is a  maximum of N x 100K rows (random, different choices of media per email). This will not be exactly N rows per email because of the duplicates (which the DISTINCT will remove):

INSERT INTO emails_media_xref
    (emails_id, media_id)
SELECT DISTINCT
    emails.id, 
    ( SELECT media.id
      FROM media
      ORDER BY RAND()
      LIMIT 1
    ) 
FROM emails 
  CROSS JOIN 
    ( SELECT 1
      FROM media
      LIMIT @N           -- pick a @N here
    ) AS x ;


If you want to insert a fixed number of rows, say 2000, totally randomly.
Warning, this may take even more time than the first query:

INSERT INTO emails_media_xref
    (emails_id, media_id)
SELECT 
    emails.id, media.id 
FROM emails 
  CROSS JOIN 
     media 
ORDER BY RAND()
LIMIT 2000 ;