mysql - DELETE based on select -

i have 1 complicated select, fills temporary table ids, based on 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 mytable concat(first_id, 'x', second_id, 'x', third_id) in (select concat(first_id, 'x', second_id, 'x', third_id) temp_table);

but query long. when show processlist, see query time 4000s.

so question is, how can optimise delete query, delete rows 1 table based on select table , delete rows based on 3 keys?

you can start removing concat , use join instead of subquery, try :

delete mt mytable mt inner join temp_table tt on mt.first_id = tt.first_id , mt.second_id = tt.second_id , mt.third_id = tt.third_id

TY