Is this normal? update query

[ianicr]

I've been running an update query. This is what I have if I do mysqladmin -pxxxxx processlist:
| 2225 | root | localhost | may | Query | 479083 | updating reference tables | update maytcf,urns set maytcf.URN = urns.URN where maytcf.OSISID = urns.OSISID |

the maytcf table contains 21,000,000 records and the urns table contains 24,000,000 records. The thing that I'm bothered about is the time its taken to run (about 5 days now). The machine its running on is a Xeon 2.6Ghz, 1Gb RAM, Redhat 9 Linux box. The first time I ran the query it took 6 hours. What could be the problem now?

Thanks

 

[TonyGroves]

It might be more efficient to say:[tt]
UPDATE maytcf JOIN urns ON maytcf.OSISID = urns.OSISID
SET maytcf.URN = urns.URN[/tt]
I don't know if MySQL processes the two queries differently, but I wonder is it possible that in the case of the query you are running, the unqualified join is done first and then the resulting set of 504,000,000,000,000 records is processed? However, with the JOIN..ON syntax, the join is already qualified, eliminating the possibility of such inefficiency.

Also, I know this is obvious, but have you got indexes on both OSISID fields?


-----
ALTER world DROP injustice, ADD peace;

 

[ianicr]

Thanks for the quick response. Yes both OSISID fields are indexed. How do I find out if as you say its running the unqualified join?

 

[TonyGroves]

I was probably barking up the wrong tree there. I did a quick test on my own test server, using similar queries on smaller tables, and the results showed little difference between the two approaches. So you can probably rule out that one.

If the query still hasn't finished, then maybe it's just "stuck" for some reason, and needs to be run again.

-----
ALTER world DROP injustice, ADD peace;