MySQL Forums
Forum List  »  Performance

Speed up Simple "update" query
Posted by: Stanley Wong
Date: April 13, 2022 01:45AM

InnoDB 5.7.33 MYSQL RDS - How to speed up this "update" query

The simple update statement took more than 2 minutes to run .I have try change it to EXISTS and the result is still the same .

I try to remove the index for column "updated" but still slow . ID is the primary key of the shipment table . shipment_id and trackingNo both are indexed. NO blocking when the update is running,

query_cache_size 1048576
query_cache_type OFF

update shipment set updated=current_timestamp where id in (SELECT shipment_id from packages where tracking_no ='AC125279615BG');

update shipment A set updated=current_timestamp where EXISTS (SELECT shipment_id from packages B where tracking_no ='AC125279615BG' AND A.ID = B.SHIPMENT_ID);

"id" "select_type" "table" "partitions" "type" "possible_keys" "key" "key_len" "ref" "rows" "filtered" "Extra"
"1" "UPDATE" "shipment" \N "index" \N "PRIMARY" "8" \N "12702773" "100.00" "Using where"
"2" "DEPENDENT SUBQUERY" "packages" \N "ref" "fk_shpt_units_shipment1_idx,shpt_package-tracking_no" "shpt_package-tracking_no" "138" "const" "1" "10.00" "Using where; Using index"

Options: ReplyQuote

Written By
Speed up Simple "update" query
April 13, 2022 01:45AM

Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.