At 02:58 PM 12/6/2004, robert_difalco wrote:

> rset = exec( "SELECT ChildID FROM Parent WHERE Foo = some_num" );
> while ( rset.next() )
> exec( "DELETE FROM Child WHERE Id = " + rset.getLong( 1 ) );
>
>Orders of magnitutede faster than this:
>
> DELETE FROM Child
> WHERE Id IN( SELECT ChildID FROM Parent WHERE Foo = some_num );

Almost certainly because in the second case, the optimizer decides
that a delete has to use the child table as the outer loop and
looks at all children. I know you've tried a dozen things, but
have you tried

DELETE FROM child c
WHERE c.id = (SELECT MAX (c1.id)
FROM child c1
JOIN parent p ON p.child_id = c1.id
WHERE p.ID = some_num)


Regards,


Ann