Update with join takes 9 to 30 hours for some reason?

Vote count:

0

I have two tables, lets called them table_A and table_B. Table_A has about 10 million rows. Table_B has 300 million rows. I created indexes on Table_B columns X and Y.

Is it normal that the updates would take this long with the number of rows I have ? This seems extremely long to me considering I have an index

Here is what they look like

Table A:

ID   BCODE 
1    A1 
2    B1  
3    C1  
4    D1  
5    F1 

Table B:

X    Y    IDX  IDY
A1   D1  
D1   F1  
C1   B1  

Table_B has columns 'X' and 'Y' that have values that are found in Table_A's column BCODE.

I run two update statements that take 9 to 30 hours to complete.

 Update Table_B
join table_A a on table_B.X = a.BCODE
set
   table_B.IDX=a.Id ;

Update Table_B
join Table_A aa on table_B.Y = aa.BCODE
set
   table_B.IDY = aa.Id  ;

Is there anyway I can speed this up ?

asked 1 min ago

Aries On The Cusp

57

Update with join takes 9 to 30 hours for some reason?