Re: Deleting orphaned records to establish Ref Integrity

>> Another idea is to try an outer join:
>> SELECT child_table.parentid INTO tmp_table
>> FROM child_table LEFT JOIN parent_table
>> ON (child_table.parentid = parent_table.parentid)
>> WHERE parent_table.parentid IS NULL;
>
>There's also
>DELETE
>FROM child_table
>WHERE NOT EXISTS (select 1
>from parent_table
>where parent_id = child_table.parent_id
>)
>
> Which won't use anything as efficient as a hash join or merge join but
will beat
> least capable of using index lookups for something basically equivalent toa
> nested loop.

Sorry for the delay, I only get to work on this system every now and then.

I tried Greg's suggestion and it worked out great. The estimates from
EXPLAIN were much larger than the actuals (e.g. 41 hours vs. 2 hours), so
I probably have some tuning to do with this dataset. I decided not to try
Tom's temp table method because I was afraid the generated table would be
very large, so the subsequent DELETE .. WHERE NOT IN (...) would cause
swapping again.

Thanks all for your help, your insights saved me a lot of headache.

Roman

_____________________________________
Consolidate your email!
http://www.fusemail.com

_____________________________________
Check All Email Accounts Anywhere!
Check your POP3 and webmail account
from any PC. With no ads
http://www.fusemail.com