Search code examples
mysqlsqlselectsql-delete

DELETE based on select

I have one very complicated SELECT, which fills temporary table with ids, based on which I need create DELETE query.

For example, temp table's rows looks like:

- first_id | second_id | third_id
 - 1   | 222 | 342
 - 1   | 222 | 343
 - 1   | 223 | 551
  • ...

And query :

DELETE FROM mytable WHERE CONCAT(first_id, 'X', second_id, 'X', third_id) IN (SELECT CONCAT(first_id, 'X', second_id, 'X', third_id) FROM temp_table);

But this query IS too long. When I show PROCESSLIST, I see that this query time is about 4000s.

So my question is, how can I optimise DELETE query, to delete rows from one table based on select FROM another table and delete just rows based on 3 keys?

Solution

  • You can start by removing the CONCAT and use an JOIN instead of a subquery, try something like :

    DELETE mt FROM mytable mt INNER JOIN temp_table tt ON mt.first_id = tt.first_id AND mt.second_id = tt.second_id AND mt.third_id = tt.third_id