MySQL - Creating sort index and update query takes forever
You have joined HST on PCL but ON condition refers to the HST itself and there is no relation between HST and PCL. The table PCL is fully scanned and the only restriction used is the WHERE p.BS = 'B' AND p.SrNo &lt;18000 that has no corresponding index. 



UPDATE


  I want to fetch the date when the value of HST.HP first exceeds or equals PCL.T1. 


UPDATE PCL AS pp
  JOIN ( SELECT h.Date AS date_the_value_first_exceeded
           FROM PCL AS p
           JOIN HST AS h  ON h.Date = p.Date      -- Joining tables by dates
          WHERE h.HP &gt;= p.T1                      -- The condition you want
          ORDER BY p.Date ASC                     -- Order the result chronologically 
          LIMIT 1                                 -- Single line required
       ) AS dd                                    -- Unrestricted join, each to each 
   SET pp.T1A = dd.date_the_value_first_exceeded
 WHERE pp.BS = 'B'
   AND pp.SrNo &lt; 18000
;


The version with MIN() function:

UPDATE PCL AS pp
  JOIN ( SELECT MIN(h.Date) AS date_the_value_first_exceeded
           FROM PCL AS p
           JOIN HST AS h  ON h.Date = p.Date      -- Joining tables by dates
          WHERE h.HP &gt;= p.T1                      -- The condition you want
       ) AS dd                                    -- Unrestricted join, each to each 
   SET pp.T1A = dd.date_the_value_first_exceeded
 WHERE pp.BS = 'B'
   AND pp.SrNo &lt; 18000
;