> My next question is: what is the difference between "select" and "delete"?
> There is another table that has one foreign key to reference the test
> (parent) table that I am deleting from and this foreign key does not have
> an index on it (a 330K row table).
The difference is that with SELECT you're not performing any modifications
to the data, while with DELETE you are. That means that with DELETE you
may have a lot of overhead due to FK checks etc.
Someone already pointed out that if you reference a table A from table B
(using a foreign key), then you have to check FK in case of DELETE, and
that may knock the server down if the table B is huge and does not have an
index on the FK column.
> Deleting one row at a time is fine: delete from test where pk_col = n1;
>
> but deleting the big chunk all together (with 80K rows to delete) always
> hangs: delete from test where cola = 'abc';
>
> I am wondering if I don't have enough memory to hold and carry on the
> 80k-row delete.....
> but how come I can select those 80k-row very fast? what is the difference
> between select and delete?
>
> Maybe the foreign key without an index does play a big role here, a
> 330K-row table references a 29K-row table will get a lot of table scan on
> the foreign table to check if each row can be deleted from the parent
> table... Maybe select from the parent table does not have to check the
> child table?
Yes, and PFC already pointed this out.
Tomas