2

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.

4
  • 1
    You should have a test system. Can't you perform tests? Profile both queries - with realistic datasets - the optimiser can change the plan depending on data volumes.
    – Vérace
    Jun 12, 2015 at 12:54
  • The version of MySql here used (5.5) does not support explain on delete statements. And yes, I can do tests. Thought I will get some answers here before running the tests.
    – gaganbm
    Jun 12, 2015 at 12:57
  • I scratched the EXPLAIN - you should be able to PROFILE. Both queries should work - it's very difficult to give further information without CPU/RAM + disk, network and app configurations. There does come a point where you have to test on your own system :-(
    – Vérace
    Jun 12, 2015 at 13:10
  • Test, as @Vérace suggests. The result will very probably be in favour of join but it never hurts to test. Jun 12, 2015 at 16:14

1 Answer 1

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.