deleting orphans in MYSQL using left join

In MYSQL I have table parent which has 2 foreign keys in table child(a_child_id and b_child_id). I am trying to delete the child orphans(the entries in table child that's not related to table parent)

select c.child_id fro开发者_C百科m child c left join parent w on (w.a_child_id=c.child_id or 
w.b_child_id=c.child_id ) where w.parent_id is null

the above query has been running for 24 hours due to the large table sizes, I just want to make sure that the query is correct?

---

Well, a SELECT query isn't going to delete anything, so it's wrong for that reason.

I'd try this:

DELETE c FROM child AS c
LEFT OUTER JOIN parent AS w1 ON c.child_id = w1.a_child_id
LEFT OUTER JOIN parent AS w2 ON c.child_id = w2.b_child_id
WHERE w1.a_child_id IS NULL AND w2.b_child_id IS NULL;

It may seem strange to join to the parent table twice, but it will probably utilize indexes better (assuming you have indexes on a_child_id and b_child_id).

You should verify the optimization plan with EXPLAIN. Convert the delete to a SELECT and get the optimization plan:

mysql> explain select c.child_id from child c 
left outer join parent w1 on c.child_id = w1.a_child_id 
left outer join parent w2 on c.child_id = w2.b_child_id 
where w1.a_child_id is null and w2.b_child_id is null\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: c
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 5
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: w1
         type: ref
possible_keys: ac
          key: ac
      key_len: 9
          ref: test.c.child_id
         rows: 1
        Extra: Using where; Using index
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: w2
         type: ref
possible_keys: bc
          key: bc
      key_len: 9
          ref: test.c.child_id
         rows: 1
        Extra: Using where; Using index

When it says "using index" it means that it's able to resolve the table access by using only the index, without needing to read the table rows.

I tested the above with MySQL 5.5.12, guessing your table structure from your description. I created a single-column index on each of a_child_id and b_child_id.

---

Wouldn't this be easier?

delete from child
where a_child_id is null
and b_child_id is null;

Or do you not have these defined as foreign keys?