MySQL Forums
Forum List  »  Newbie

Re: MySQL Update query time-out
Posted by: Peter Brawley
Date: October 05, 2020 01:32PM

What is the MySQL version? How much RAM is there? What is innodb_buffer_pool_size?

The subquery ...select * from... is entirely redundant, so your query becomes ...

update EzyComposition
set TonPerBlok = (
  select sum(equivton) 
  from EzyComposition t2
  where EzyComposition.vessel = t2.vessel 
    AND EzyComposition.grower = t2.grower 
    AND EzyComposition.block = t2.block 
    AND EzyComposition.section = t2.section 
);

This sets the row-by-row column value of `tonperblok` to sum(`eqiivton`) in rows that match on `vessel`,`grower`,`block`,`section; that's what's intended? Storing self-referential sums can be problematic for data integrity; what's the compelling reason you're not keeping this sum in another table?

If that can't be fixed, let's see the results of ...

Show Create Table EzyComposition;

Explain Extended 
update EzyComposition
set TonPerBlok = (
  select sum(equivton) 
  from EzyComposition t2
  where EzyComposition.vessel = t2.vessel 
    AND EzyComposition.grower = t2.grower 
    AND EzyComposition.block = t2.block 
    AND EzyComposition.section = t2.section 
);

Options: ReplyQuote


Subject
Written By
Posted
Re: MySQL Update query time-out
October 05, 2020 01:32PM


Sorry, you can't reply to this topic. It has been closed.

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.