Insert query optimization

Question

Need your help optimising an insert query.

I have two tables in my MySQL DB. I will be inserting into Table1. Information will be retreived in Table2.

Table 2 contains a couple of million records. My insert query should look for all users who has a previous event in Table2 and insert it into Table1.

This is my insert query - but it's killing my server.

INSERT INTO Table1(
SELECT 
 d.created,
 d.userID, 
 d.eventID,
 d.previousEvent
FROM (
SELECT 
 e.created,
 e.userID,
 e.eventID,
 (SELECT eventID FROM TABLE2 pe WHERE e.userID=pe.userID AND e.created>pe.created ORDER BY pe.created DESC LIMIT 1) AS e.previousEvent
FROM 
 TABLE2 e
WHERE
 e.eventID not in (Select f.EventID from TABLE1 f)
ORDER BY 
 e.created desc
) d
WHERE
 d.preEvent is not null
GROUP by
 d.eventID,
 d.created,
 d.userID
);

eg:

Table1:

created | userID | eventID(unique) | previousEvent
2015年11月15日 66666 1045698664 4566660001

Table2:

created | userID | eventID(unique - random)
2015年11月15日 55555 4755500001
2015年11月15日 66666 4566660001
2015年11月15日 77777 8634757777
2015年11月15日 88888 1845562565
2015年11月15日 66666 1045698664

• user80685: On your tables/columns have you tried creating an index? If so what is the fragmentation level of the index(s)

Answer 1

not in (Select f.EventID from TABLE1 f)

optimizes poorly. Turn it into a LEFT JOIN:

LEFT JOIN TABLE1 ON e.EventID = f.EventID
WHERE f.EventID IS NULL