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?

The only thing coming to mind which might help is possibly adding indices on one or both tables to speed up the join in your update. — Tim Biegeleisen, Jul 03 '18 at 13:44
If you do already have the appropriate indexes and it's still slow, you should also make sure that the datatypes of isin and mic are the same between the two tables. Take a look at your `execution plan` to see if you can identify any problems (eg table scans, or the optimizer picking the wrong index) — RToyo, Jul 03 '18 at 13:48

Gordon Linoff · Accepted Answer · 2018-07-04T02:35:23.297

2

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.

edited Jul 04 '18 at 02:35

answered Jul 03 '18 at 13:54

Gordon Linoff

1,242,037
58
646
786

So for table securities_live create 3 new indexes ('Create a new index') for isin, mic and close? What about Index name and index type (Primary, Index, Unique, Fulltext)? Size? – Philipp M Jul 03 '18 at 13:59
Suggestion is to create only one index, but on multiple columns. – Kobi Jul 03 '18 at 14:10
@Kobi ... So 'index name' doesn't matter ... but what about Index type? Primary, Index, Unique, Fulltext? ... and the map it to those 3 columns? – Philipp M Jul 03 '18 at 14:14
1

A simple index => `CREATE INDEX idx_foobar ON securities_prev(isin, mic, close)` – Kobi Jul 03 '18 at 14:18
... great thanks. I already tested it and now it takes like 11 seconds ... so I'm already happy with that ... thanks! – Philipp M Jul 03 '18 at 14:21

johnny · Answer 2 · 2018-07-03T14:01:52.543

1

As the commenters have noted, since you have your indexes set, I suggest you try to do these piecemeal. Do them at 5k a time until it is complete. Try 10k. Obviously you cannot do 170k or 40k. I have had many times where a database had millions of rows, and I had to to them 100k or less at a time. This was because of the limitations of the hardware.

For example,

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

You may wish to use Order By so you know what records are what and you need keep track of what has been updated.

See here,

how can I Update top 100 records in sql server

Sorry, I just read you were using MySQL,

MySQL - UPDATE query with LIMIT

edited Jul 03 '18 at 14:01

answered Jul 03 '18 at 13:53

johnny

19,272
52
157
259

Thanks I'll give it a try. – Philipp M Jul 03 '18 at 14:00
See my edit. You don't want to update the same 1000 rows each time. – johnny Jul 03 '18 at 14:02
Yeah ... the first example caused an error. Does is automatically execute another block if I use LIMIT? – Philipp M Jul 03 '18 at 14:09
@PhilippM Not that I know of you. At this point you are doing things manually because your hardware, network, or design can't take it. – johnny Jul 03 '18 at 14:10
... setting the indexes like suggested above (which I didn't had) already helped ... with the indexes it takes like 11 sec which is fine for me. But also thanks for the help! – Philipp M Jul 03 '18 at 14:23

score -2 · Answer 3 · edited Jul 03 '18 at 14:18

-2

Try This:

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;

edited Jul 03 '18 at 14:18

Kobi

2,494
15
30

answered Jul 03 '18 at 13:51

Abdulrehman Sheikh

931
9
15

1

What is the differences between your query and the OP 'second try' ? – Kobi Jul 03 '18 at 14:09
difference is that i bind the condition inside the parentheses so the query will be easy to understand in the sections for compiler – Abdulrehman Sheikh Jul 04 '18 at 16:26

INNER JOIN ON multiple attributes not working

3 Answers3