title: how to optimize UPDATE query for better MySQL performance
body: <p>I have 2 tables in mySQL db...
  they are very large.. its about 1 million now and soon to be 5 million or so  </p>
  
  <p>one is jobseeker
  other is joomla users table</p>
  
  <p>I want to copy or insert IDs into the jobseeker table where the email column of both matches..</p>
  
  <p>i.e. jobseeker email = jos users  email.</p>
  
  <p>I used below query but it takes too much time and puts heavy load on mysql server ....the queries gets stuck and i always end-up restarting mysql ...</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>how can I optimize above query to achieve better performance. Also, I would be interested if it can be executed in batches i.e 20000 or 40000 records at time.</p>
  
  <p>Please advise</p>