Optimize this MySQL query that deletes rows missing from another table

Question

I have two tables, one "Import" and the other "Current". Every hour data is imported into Import, and I have a procedure that has to make sure to update Current with the new data from Import, for a specified date interval.

And if rows are missing from Import during that date interval it means they have been deleted, so they should also be deleted from Current. Here's where this query comes in:

delete from Current where CurrentId in
(
select CurrentId as id from (
select CurrentId from Current dd
where not exists 
(
    select d.* from Current d, Import tc
    where d.date between @startdate and @enddate
    and d.employeenumber = tc.employeenumber
    and d.date = tc.date
    and d.origin = tc.origin
    and d.planid = tc.planid
    and d.CurrentId = dd.CurrentId 
)
and dd.date between @startdate and @enddate
) as x
);

So essentially in the inner loop it selects rows that are the same in both tables. Then it makes a select from Curret "where not exists" those rows to find the ones that needs deletion. It then gets the ID's and deletes them.

But with the amount of data I have it takes an hour and that's just too long... How can I speed this up?

Can you show the results of SHOW INDEXES FROM on both tables? — Pete
– Pete, Commented Nov 3, 2017 at 17:07
No index on Import except for the auti-incrementing primary key. That's a good point. Will try setting up an index on it! — CodeOrElse
– CodeOrElse, Commented Nov 3, 2017 at 23:15

Thorsten Kettner · Accepted Answer · 2017-11-03 17:14:23Z

1

You select from current again and again and I see no reason for this. If you want to delete from current where there is no related record in import, then all you need to do is look up import, if I'm not mistaken.

delete from current c
where c.date between @startdate and @enddate
and not exists
(
  select *
  from import i
  where i.employeenumber = c.employeenumber
    and i.date           = c.date
    and i.origin         = c.origin
    and i.planid         = c.planid
);

To get this fast, you may want an index on import(date, employeenumber, origin, planid). And on current(date) of course.

answered Nov 3, 2017 at 17:14

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

CodeOrElse Over a year ago

The index is what really solved the problem for me! Thanks! Seems so silly now, why was that not the first thing I tried... :)

Collectives™ on Stack Overflow

Optimize this MySQL query that deletes rows missing from another table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related