sql: deleting lines from a table, according to another, takes too long

Question

I have some big tables and I want to remove a line with the following command

When I try this, it's taking too long:

DELETE global, lines
FROM  lines force index(cardIndex) 
INNER JOIN global force index(cardIndexes)
          ON global.card = lines.card
          WHERE lines.product NOT IN (SELECT code FROM article);

My table global has 900.000 rows, lines tables has 6.000.000 rows and article has 40.000 Any idea about what can I do to improve the command?

Answer 1

NOT IN is a very expensive operation. You can eliminate it with this alternative:

• OUTER JOIN to the article table instead, and
• Include only those rows where the article.code is null.

The null means that there's no corresponding record in the article table - in other words: "not in".

DELETE global, lines
FROM lines force index(cardIndex)
INNER JOIN global force index(cardIndexes) ON global.card = lines.card
LEFT JOIN article ON lines.product = article.code
WHERE article.code IS NULL;