MySQL update query where in subquery slow

Question

I'm having issues with MySQL query running extremely slow. It takes about 2 min for each UPDATE to process.

This is the query:

   UPDATE msn
   SET is_disable = 1
   WHERE mid IN
   (
       SELECT mid from link
       WHERE rid = ${param.rid}
   );

So my question is, I would like to know how the performance of the UPDATE statement will be affected if the result of the subquery is 0 or NULL. Because I think that maybe the process is slow because the result of the subquery is 0 or NULL.

Thanks a lot in advance.

Answer 1

The issue here is that the subquery following IN has to execute, whether or not it returns any records. I would probably express your update using exists logic:

UPDATE msn m
SET is_disable = 1
WHERE EXISTS (SELECT 1 FROM link l WHERE m.mid = l.mid AND l.rid = ${param.rid});

Then, add the following index to the link table:

CREATE INDEX idx ON link (mid, rid);

You could also try and compare against this version of the index:

CREATE INDEX idx ON link (rid, mid);