MySQL UPDATE statement times out

Question

The following very simple snippet of SQL is failing:

UPDATE smalltable,bigtable
SET smalltable.ssn=bigtable.ssn
WHERE smalltable.last = bigtable.last && smalltable.first = bigtable.first;

bigtable has 16,000 records -- not really all that big for SQL. smalltable has about 300. For some reason this statement is timing out (> 30 seconds). Why? It seems very simple, and the data isn't hard to work with: not a lot of repeats, short fields (VARCHAR(20)), etc.

Am I doing something wrong? I'm just trying to update the records in smalltable with a simple (or so I thought) lookup in bigtable.

Edit: Very possibly relevant: smalltable is a LOCAL INFILE.

Answer 1

Do you have indexes on:

smalltable.last, bigtable.last, smalltable.first, bigtable.first