Deleting from a big table

[Davide Cicuta]

Hello!
I have a big table (around 45M records and growing), say

BigTable(pk VARCHAR(64), note VARCHAR(64))

with pk the primary key, not autoincremented.
Then I have another big table, say

AnotherBigTable(pk VARCHAR(64), externalKey VARCHAR(64), someOtherStuff...)

with pk the primary key, not autoincremented. Here "externalKey" references (explicitly) another table. We can say that BigTable.pk references AnotherBigTable.pk, but since it's not always the case, the constraint is not explicit (there are other tables which could be referenced by BigTable.pk)

Both tables are InnoDB and on both tables several threads are writing, without any performance issue.

PROBLEM: I want to delete some records from BigTable.
What I want to do is

DELETE FROM BigTable
WHERE pk IN
(SELECT pk FROM AnotherBigTable WHERE externalKey='abc');

This query takes ages: I've never seen it ending!

But

SELECT pk FROM AnotherBigTable WHERE externalKey='abc'

is immediate and, say it returns '123' and '234', also

DELETE FROM BigTable WHERE pk IN ('123, '234')

is immediate.

Queries occur per primary key, so they should be indexed...

Do you have a workaround or an explanation for this behaviour?

Thank you very much in advance!