MySQL Forums
Forum List  »  Stored Procedures

Re: Need your help optimising an insert query
Posted by: Peter Brawley
Date: November 16, 2015 04:30PM

The Select will fail because preEvent isn't defined in the subquery. Presumably you meant previousevent, the alias the subquery uses.

The outer query just excludes null previousevents and duplicates, and imposes an order by; all that can be folded into the main inner query or the Insert logic (see below):

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.previousevent is not null and e.eventid not in ( select eventid from table1 ) 
order by e.eventid, e.created, e.userid ;

Now the Where clause: In( Select... ) doesn't optimise well in MySQL (the earlier the version, the worse the performance); it'd probably perform better as an exclusion join. But all it does is exclude eventIds that already exist in table1, and you can accomplish that with Insert Ignore if table1.eventid is a primary or unique key.

And the query is just fodder for an Insert, so Order By useless.

Then we have ...

insert ignore into table1
select 
  created, 
  userid, 
  eventid, 
  (select eventid 
   from table2 pe 
   where table2.userid=pe.userid and table2.created>pe.created 
   order by pe.created desc limit 1
   ) as previousevent 
from table2 
where previousevent is not null;

which you might want to compare with ...

insert ignore into table1
select 
  created, 
  userid, 
  eventid, 
  (select max(eventid) 
   from table2 pe 
   where table2.userid=pe.userid and table2.created>pe.created 
  ) as previousevent 
from table2 
where previousevent is not null;

See if Explain Extended likes that better than the original?

If it's still slow, you might move the correlated subquery logic out to a join.

Options: ReplyQuote


Subject
Views
Written By
Posted
2248
November 16, 2015 04:22AM
Re: Need your help optimising an insert query
846
November 16, 2015 04:30PM


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.