Good morning i am fairly new to SQL and have been self teaching myself various things and i have stumbled upon something that well i just cant wrap my head around and wanted to see what the experts could tell me.
I have two table one is called phase3 the other is called cqt.
phase 3 has the following columns: num (not null auto increment), stored (decimal not null), linking (int foreign key (schema1.cqt(trayid)) not null), trayid (int zero fill), pallet (int zero fill)
cqt has the following columns: trayid (int primary key not null), trayact (int not null), traytype (varchar(5)), pallet (int not null)
Phase3 has 995,865 records in it
cqt has 1270 records in it.
trayid from cqt is unique and has a 1 to about 160 reference to linking in phase3. which is why i made trayid primary and linking foreign and the two are referenced.
So now i am trying to find a way to make the fastest query possible that will take the trayact and pallet from cqt and update phase3 with these values.
So I first wrote the following query just to see how long it would take.
select a.stored, b.trayact, b.pallet from schema1.phase3 as a
join schema1.cqt as b on b.trayid = a.linking
When i run the above query it takes about 15 seconds to return results. So i am like ok now there will be times when b.pallet is a null but there is a value in a.backed that i can use just to keep the value from being a null. So i first wrote the following to see what would happen.
select a.stored, b.trayact, ifnull(b.pallet,999) from schema1.phase3 as a
join schema1.cqt as b on b.trayid = a.linking
This worked out fine as well took about 15 to 20 seconds to run as well so now i substituted the 999 with a.backed. So now it looked like this.
select a.stored, b.trayact, ifnull(b.pallet,a.backed) from schema1.phase3 as a
join schema1.cqt as b on b.trayid = a.linking
Now all of a sudden it takes like 12min which is totally unacceptable Why did it go from 20sec to 12min, well that i do not know and i dont understand. And when i try to do this as an update. well that takes approx 20 to 22 min again this is unacceptable. So what am i missing?
Here is the way i formed the update from select and this is the first time i have had something like this happen. I have written other updates from selects that never ever took this long.
update schema1.phase3 as a
join schema1.cqt as b on b.trayid = a.linking
set a.trayid = b.trayact, a.pallet = b.pallet ;