INNER JOIN ON multiple attributes not working

Question

I need to update a column with values from another table ... but it takes forever or aborts. A specific security (isin) can be listed at multiple exchanges (mic) ... so I think I need to have two conditions in the INNER JOIN ... ON (??). Are my attempts below correct? I have about 170,000 records in the table with 40,000 unique isins.

First try:

SQL:
SET SESSION SQL_BIG_SELECTS = 1;
UPDATE securities_live t1
INNER JOIN securities_prev t2
    ON t1.isin = t2.isin AND t1.mic = t2.mic
SET t1.prev_close = t2.close;

Second try:

SQL:
SET SESSION SQL_BIG_SELECTS = 1;
UPDATE securities_live t1
INNER JOIN securities_prev t2
    ON (t1.isin = t2.isin AND t1.mic = t2.mic)
SET t1.prev_close = t2.close;

Edit regarding indexes for both tables at the moment:

Indexes (securities_live):
Primary|Unique=Yes|Packed=no|Column=id|Cardinality=166776|Collation=A

Indexes (securities_prev):
Primary|Unique=Yes|Packed=no|Column=id|Cardinality=166776|Collation=A

In both tables I have a primary key on column 'id'. So e.g. in table securities_live 'Create a new index' one for column isin and another one for column mic? What about Index name and index type (Primary, Index, Unique, Fulltext)? Size?

Answer 1

For this query:

UPDATE securities_live t1 INNER JOIN
       securities_prev t2
       ON t1.isin = t2.isin AND t1.mic = t2.mic
    SET t1.prev_close = t2.close;

I would suggest an index on securities_prev(isin, mic, close).

However, I suspect that you are updating all or almost all records. If that is the case, it is usually faster to truncate the table and re-populate it with insert. Update is best used for updating a relatively small number of rows.