0

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
  • On your tables/columns have you tried creating an index? If so what is the fragmentation level of the index(s) – user80685 Nov 16 '15 at 10:27
0

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
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.