How to optimize an UPDATE query for better MySQL performance

Question

I have 2 tables in mySQL db ... they are very large .. its about 1 million now and soon there will be 5 million or so

one - job seeker another - joomla user table

I want to copy or paste identifiers into the jobseeker table, where the email column is both matches.

i.e. email address = jos email users.

I used the query below, but it takes too much time and puts a lot of stress on the mysql server .... the requests get stuck and I always finish restarting mysql ...

UPDATE `jos_jbjobs_jobseeker` SET user_id = ( SELECT jos_users.id FROM jos_users WHERE jos_users.email = jos_jbjobs_jobseeker.email) WHERE EXISTS ( SELECT jos_users.id FROM jos_users WHERE jos_users.email = jos_jbjobs_jobseeker.email); 

how can I optimize the above query to achieve better performance. In addition, I would be interested if it can be performed in batches, i.e. 20,000 or 40,000 entries per time.

Please inform

Answer 1

Try the following:

 UPDATE jos_jbjobs_jobseeker a INNER JOIN jos_users b ON a.email = b.email SET a.user_id = b.id 

Answer 2

How about this simple request?

 UPDATE jos_jbjobs_jobseeker jjj JOIN jos_users ju ON jjj.email = ju.email SET jjj.user_id = ju.id;