MySQL Forums
Forum List  »  Newbie

Re: update queries taking 1 week to finish
Posted by: Peter Brawley
Date: August 25, 2014 11:10AM

If the idiot who designed those tables hasn't been fired, he needs to be.

Naming columns ...1, ...2 &c where queries must treat those numerical indices as data, requiring looping constructs as in your code, is one of the worst possible database design mistakes. Your ludicrously bad Update performances exacts the price. Both tables need to be migrated to a design like this ...

n tinyint
idx tinyint (taking values corresponding to indices in n1,n2 &c

And the date column need to become a proper date.

For example ...

drop table if exists newresult;
create table newresult(date date, n tinyint, idx tinyint, primary key (date, n, idx);
insert into newresult(date, n, idx, whenadded ) select str_to_date(date,'%m/%d/%Y'), n1, 1, whenadded from oldresult;
insert into newresult(date, n, idx, whenadded ) select str_to_date(date,'%m/%d/%Y'), n2, 2, whenadded from oldresult;
insert into newresult(date, n, idx, whenadded ) select str_to_date(date,'%m/%d/%Y'), n3, 3, whenadded from oldresult;
insert into newresult(date, n, idx, whenadded ) select str_to_date(date,'%m/%d/%Y'), n4, 4, whenadded from oldresult;
insert into newresult(date, n, idx, whenadded ) select str_to_date(date,'%m/%d/%Y'), n5, 5, whenadded from oldresult;
insert into newresult(date, n, idx, whenadded ) select str_to_date(date,'%m/%d/%Y'), n6, 6, whenadded from oldresult;
insert into newresult(date, n, idx, whenadded ) select str_to_date(date,'%m/%d/%Y'), n7, 7, whenadded from oldresult;

(Adjust the str_to_date arg if the varchar date is not in US date format.)

And similarly for the other table.

Then you can write simple update queries that will execute in reasonable time.

Options: ReplyQuote


Subject
Written By
Posted
Re: update queries taking 1 week to finish
August 25, 2014 11:10AM


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.