update queries taking 1 week to finish

[rol and]

i got handed a mysql server with a bash cronjob.


whoever wrote that script did a loop to match n1,n2,n3,n4,n5,n6 in oldresult to result. and once found, to update result.status


the goal of this weird way of doing things is to update result.status whenever fields in oldresult match any combination of same fields in result.

i'm pretty sure there's a smarter way of doing this in sql though i don't know how.

mysql> describe result;
+-------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------+------+-----+---------+-------+
|numbers_trig | int(11) | NO | | NULL | |
| n1 | tinyint(1) | YES | MUL | NULL | |
| n2 | tinyint(1) | YES | | NULL | |
| n3 | tinyint(1) | YES | | NULL | |
| n4 | tinyint(1) | YES | | NULL | |
| n5 | tinyint(1) | YES | | NULL | |
| n6 | tinyint(1) | YES | | NULL | |
| status | tinyint(1) | YES | | NULL | |
+-------------+------------+------+-----+---------+-------+
8 rows in set (0.00 sec)


mysql> describe oldresult
-> ;
+-----------+-------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+-------------------+-------+
| date | varchar(12) | YES | MUL | NULL | |
| n1 | tinyint(1) | YES | | NULL | |
| n2 | tinyint(1) | YES | | NULL | |
| n3 | tinyint(1) | YES | | NULL | |
| n4 | tinyint(1) | YES | | NULL | |
| n5 | tinyint(1) | YES | | NULL | |
| n6 | tinyint(1) | YES | | NULL | |
| n7 | tinyint(1) | YES | | NULL | |
| whenadded | timestamp | NO | | CURRENT_TIMESTAMP | |
+-----------+-------------+------+-----+-------------------+-------+
9 rows in set (0.00 sec)


any advice ?

the script is as such:

for n1 in {1..6};do for n2 in {1..6};do for n3 in {1..6};do for n4 in {1..6};do for n5 in {1..6};do for n6 in {1..6};do
mysql oldresult -e "UPDATE result INNER JOIN numbers_trig ON numbers_trig.n1 = result.n$n1 AND numbers_trig.n2 = result.n$n2 AND numbers_trig.n3 = result.n$n3 AND numbers_trig.n4 = result.n$n4 AND numbers_trig.n5 = result.n$n5 and numbers_trig.n6 = result.n$n6 SET result.status= 6;";
mysql oldresult -e "UPDATE result INNER JOIN numbers_trig ON numbers_trig.n1 = result.n$n1 AND numbers_trig.n2 = result.n$n2 AND numbers_trig.n3 = result.n$n3 AND numbers_trig.n4 = result.n$n4 AND numbers_trig.n5 = result.n$n5 SET result.status= 5;";
mysql oldresult -e "UPDATE result INNER JOIN numbers_trig ON numbers_trig.n1 = result.n$n1 AND numbers_trig.n2 = result.n$n2 AND numbers_trig.n3 = result.n$n3 AND numbers_trig.n4 = result.n$n4 and numbers_trig.n6 = result.n$n6 SET result.status= 5;";
mysql oldresult -e "UPDATE result INNER JOIN numbers_trig ON numbers_trig.n1 = result.n$n1 AND numbers_trig.n2 = result.n$n2 AND numbers_trig.n4 = result.n$n4 AND numbers_trig.n5 = result.n$n5 and numbers_trig.n6 = result.n$n6 SET result.status= 5;";
mysql oldresult -e "UPDATE result INNER JOIN numbers_trig ON numbers_trig.n1 = result.n$n1 AND numbers_trig.n3 = result.n$n3 AND numbers_trig.n4 = result.n$n4 AND numbers_trig.n5 = result.n$n5 and numbers_trig.n6 = result.n$n6 SET result.status= 5;";
mysql oldresult -e "UPDATE result INNER JOIN numbers_trig ON numbers_trig.n2 = result.n$n2 AND numbers_trig.n3 = result.n$n3 AND numbers_trig.n4 = result.n$n4 AND numbers_trig.n5 = result.n$n5 AND numbers_trig.n6 = result.n$n6 SET result.status= 5;";
mysql oldresult -e "UPDATE result INNER JOIN numbers_trig ON numbers_trig.n7 = result.n$n1 AND numbers_trig.n2 = result.n$n2 AND numbers_trig.n3 = result.n$n3 AND numbers_trig.n4 = result.n$n4 AND numbers_trig.n5 = result.n$n5 and numbers_trig.n6 = result.n$n6 SET result.status= 7;";
mysql oldresult -e "UPDATE result INNER JOIN numbers_trig ON numbers_trig.n1 = result.n$n1 AND numbers_trig.n7 = result.n$n2 AND numbers_trig.n3 = result.n$n3 AND numbers_trig.n4 = result.n$n4 AND numbers_trig.n5 = result.n$n5 and numbers_trig.n6 = result.n$n6 SET result.status= 7;";
mysql oldresult -e "UPDATE result INNER JOIN numbers_trig ON numbers_trig.n1 = result.n$n1 AND numbers_trig.n2 = result.n$n2 AND numbers_trig.n7 = result.n$n3 AND numbers_trig.n4 = result.n$n4 AND numbers_trig.n5 = result.n$n5 and numbers_trig.n6 = result.n$n6 SET result.status= 7;";
mysql oldresult -e "UPDATE result INNER JOIN numbers_trig ON numbers_trig.n1 = result.n$n1 AND numbers_trig.n2 = result.n$n2 AND numbers_trig.n3 = result.n$n3 AND numbers_trig.n7 = result.n$n4 AND numbers_trig.n5 = result.n$n5 and numbers_trig.n6 = result.n$n6 SET result.status= 7;";
mysql oldresult -e "UPDATE result INNER JOIN numbers_trig ON numbers_trig.n1 = result.n$n1 AND numbers_trig.n2 = result.n$n2 AND numbers_trig.n3 = result.n$n3 AND numbers_trig.n4 = result.n$n4 AND numbers_trig.n7 = result.n$n5 AND numbers_trig.n6 = result.n$n6 SET result.status= 7;";
mysql oldresult -e "UPDATE result INNER JOIN numbers_trig ON numbers_trig.n1 = result.n$n1 AND numbers_trig.n2 = result.n$n2 AND numbers_trig.n3 = result.n$n3 AND numbers_trig.n4 = result.n$n4 AND numbers_trig.n5 = result.n$n5 AND numbers_trig.n7 = result.n$n6 SET result.status= 7;";done;done;done;done;done;echo $i;done