Update query takes too much time with 26.4 million rows

Asked Aug 31 '15 at 11:21

Active Aug 31 '15 at 11:21

Viewed 111 times

I have more than 26.4 million rows in table a "item"to be updated. i am using this query to update but it takes too much time aprox more than 4 hours and continue running. Is it correct query or there is better way to update it? Need your help

UPDATE item a
JOIN cost b ON a.order_id = b.order_id
           AND YEAR(a.date) = b.YEAR
           AND month(a.date) = b.month
           AND hour(a.time) = b.hour
SET a.cost = b.cost

query was obtained from below discussion it is running but taking too much time.

Update table if conditions met. (update the value from table b to a) corresponding value required

edited May 23 '17 at 11:52

Community

asked Aug 31 '15 at 11:21

Kamran Mushtaq

Is order_id a primary key? – Rahul Tripathi Aug 31 '15 at 11:23
order_id is not primary key but both tables are linked with order_id field (same field in both tables "item" & "cost") – Kamran Mushtaq Aug 31 '15 at 11:25
How large is table `b`? Perhaps 4 hours isn't so bad, if, for instance, all the rows are being updated. – Gordon Linoff Aug 31 '15 at 11:35
Gordon Linoff-- table b "cost" have aroung 97K rows. time is 4 hours and continued – Kamran Mushtaq Aug 31 '15 at 11:49

Update query takes too much time with 26.4 million rows

0 Answers0