mysql更新语句慢,更新语句中的MySQL查询缓慢

I am trying to move some data from a database to another. I am currently having over a million entries in my database and I was expecting this to take long but already passed 50min and no result :) .

Here is my query:

UPDATE xxx.product AS p

LEFT JOIN xx.tof_art_lookup AS l ON p.model_view = l.ARL_SEARCH_NUMBER

SET p.model = l.ARL_DISPLAY_NR

WHERE p.model_view = l.ARL_SEARCH_NUMBER;

Any help on how to improve this query will be welcome. Thanks in advance!

解决方案

Indexes on p.model_view, l.ARL_SEARCH_NUMBER if you not gonna get rid of JOINs.

Actually, it might be optimized depending on actual data amounts and their values (NULLs presence) by use of:

1. Monitoring query execution plan and , if it's not good, putting query hints for compiler or exchange JOINs for subqueries so compiler uses another type of join inside it (merge/nested loops/hashs/whatever)

2. Making a stored procedure with more comlicated but faster logic

3. Doing updates by small portions