I need to delete rows from a table based on what is present in a temporary table.
For me, both of these statements work :
DELETE from main_table where `id` in (select `deletable_id` from temporary_table);
and
DELETE main_table from main_table join temporary_table on main_table.id = temporary_table.deletable_id;
Which of the two is advisable to use given the fact that main_table will be having a billion of rows and the temporary will have a few thousands.
In the given scenario where the main_table has billions of rows and the temporary_table has only a few thousand rows, the second statement would generally be more efficient
DELETE main_table FROM main_table JOIN temporary_table ON main_table.id = temporary_table.deletable_id;
Rationale :
an inner join between the main_table and temporary_table based on the id column, allows for a direct match and elimination of rows to be deleted.
On the other hand, the first statement uses a subquery with the IN clause, which needs to be executed for each row in the main_table, resulting in potentially slower performance.
P.S.
That is assuming there is Indexing on the Join column and stats updated periodically. If there's no indexing and stats there then still it will be good . Only with stale stats will the sub-query show up as closely competitive but for so many rows in the main table I doubt if it will still come close to the IJ
