I woul like to Reduce MYSQL Query Execution time.my query taking long time?

Following MYSQL Query Taking Long Time To Execute How to reduce execution time any solution? The table contains more than 1000 records I am trying to update sort order (prd_sort_order) based on price (prd_price) in a product table and using codeigniter

SQl

SET @rank = 0;      UPDATE tbl_products p SET p.prd_sort_order=@rank:=@rank+1       WHERE p.prd_booking_status=1          AND p.prd_status=1       ORDER BY p.prd_price DESC            SELECT MAX(prd_sort_order) as prd_sort_order FROM tbl_products       WHERE prd_booking_status = 1         AND prd_status=1  

I converted into codeigniter as follows

  $this->db->query("SET @rank = 0;");    $avbl_veh = $this->db->query("UPDATE " . $this->table . " p SET p.`prd_sort_order`=@rank:=@rank+1         WHERE p.`prd_booking_status`=1 AND p.`prd_status`=1 ORDER BY p.`prd_price` DESC");    $qry = $this->db->query('SELECT MAX(`prd_sort_order`) as `prd_sort_order` FROM ' . $this->table            . ' WHERE `prd_booking_status` = 1 AND `prd_status`=1');    $res = $qry->row_array();    $max_avbl_veh = !empty($res['prd_sort_order']) ? $res['prd_sort_order'] : 0;       $this->db->query("SET @rank = " . $max_avbl_veh);    $bkd_veh = $this->db->query("UPDATE " . $this->table . " p SET p.`prd_sort_order`=@rank:=@rank+1              WHERE p.`prd_booking_status`= 13 AND p.`prd_status`=1 ORDER BY p.`prd_price` DESC");      $qry = $this->db->query('SELECT MAX(`prd_sort_order`) as `prd_sort_order` FROM ' . $this->table            . ' WHERE `prd_booking_status` = 13 AND `prd_status`=1');    $res = $qry->row_array();    $max_bkd_veh = !empty($res['prd_sort_order']) ? $res['prd_sort_order'] : 0;      $this->db->query("SET @rank = " . $max_bkd_veh);      $bkd_veh = $this->db->query("UPDATE " . $this->table . " p SET p.`prd_sort_order`=@rank:=@rank+1              WHERE p.`prd_booking_status`= 40 AND p.`prd_status`=1 ORDER BY p.`prd_price` DESC");    $qry = $this->db->query('SELECT MAX(`prd_sort_order`) as `prd_sort_order` FROM ' . $this->table            . ' WHERE `prd_booking_status` = 40 AND `prd_status`=1');    $res = $qry->row_array();     

https://stackoverflow.com/questions/67266165/i-woul-like-to-reduce-mysql-query-execution-time-my-query-taking-long-time April 26, 2021 at 07:54PM