Performance of DELETE with NOT IN (SELECT ...)
I'm a big fan of the "anti-join." This works efficiently for both large and small datasets:
delete from ms_author mawhere not exists ( select null from author a where ma.name = a.name)
Your delete query using NOT IN usually result in a nested loop antijoin which will result in poor performance. You can rewrite your query as follows:
You can write something like this:
DELETE FROM ms_author AS mWHERE m.id IN (SELECT m.id FROM ms_author AS m LEFT JOIN author AS a ON m.name = a.name WHERE a.name IS NULL);This approach has as additional advantage that you are using the primary key 'id' to delete rows and this should be much faster.