MySQL Forums
Forum List  »  Stored Procedures

Need your help optimising an insert query
Posted by: Lunen DL
Date: November 16, 2015 04:22AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Need your help optimising an insert query
2069
November 16, 2015 04:22AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.