title: Slow sql update query on big tables
body: <p>I have writter sql query and is fine on smaller tables but with 100 thousands records the query is just executing but never performs. Here is my query, Can some one make a faster version of the query.</p>
  <pre><code>UPDATE members_tb mtb
  INNER JOIN
    (SELECT DISTINCT subscription_id,
                     mst.member_id,
                     mst.expiry_date
     FROM member_subscription_tb mst
     INNER JOIN
       (SELECT member_id,
               max(expiry_date) AS expiry_date
        FROM member_subscription_tb
        GROUP BY member_id
        LIMIT 0,
              1000) t ON mst.expiry_date = t.expiry_date
     AND t.member_id=mst.member_id) mstb ON mtb.member_id = mstb.member_id
  SET mtb.subscription_id = mstb.subscription_id
  </code></pre>
  <p>Regards,
  Hamayun</p>
  <p><strong>-----Edit-----</strong></p>
  <p>I have two tables members and members_subscriptions
  members have members and subscriptions has members subscriptions
  one to many relationship.
  Now in members table subscription_id is null
  I want to update it with subscription_id from members_subscriptions where Max(expiry_date) i.e date expiry is grater and members_id is equal to members.members_id</p>