CODEWITHSUNDEEP
mysqlfreezesql-update
Charles
Charles

Reputation: 11479

MySQL UPDATE statement times out

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.

Upvotes: 1

Views: 678

Answers (1)

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171351

Do you have indexes on:

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

Upvotes: 5

Related Questions