mysql> SELECT VERSION();
+------------------+
| VERSION() |
+------------------+
| 5.5.62 |
+------------------+
1 row in set
On my MySql RDBMS I have two tables
tbl_old , number of rows 745.088
tbl_new , number of rows 115.127
I need update on tbl_new the column cod_New with the value on tbl_old from column cod_Old
I have tried this SQL query but is very long time on execute
UPDATE `tbl_new` jjj,
`tbl_old` kkk
SET jjj.`cod_New` = kkk.`cod_Old`
WHERE
jjj.`COD_ORG_NEW` = kkk.`COD_ORG_OLD`
AND CASE
WHEN jjj.`TYPE_2` = 'SC' THEN
cod_series = 2
WHEN jjj.`TYPE_2` = 'SP' THEN
cod_series = 3
ELSE
cod_series = 4
END
AND jjj.`element_New` = kkk.`element_Old`;
inserting indexes of the columns involved on the two tables don’t change the situation
I don’t understand your case statements in your WHERE clause. 'cod_series = 2" is not generally a boolean; cod_series is undefined or at least ambiguous to me without a schema. What are you trying to do there?
For the record, i’m going to be guessing for the most part. Query optimization isnt exactly something i’ve studied. So… grain of salt, and i’m sure @r937 will be along shortly to tell us how it should be done
I’m not sure there’s going to be a ‘quick’ solution here, as you’re dealing with a hundred thousand rows.
The back of my head wants to say it may speed things up to explicitly define the join between the tables rather than asking the database to make the implicit inner join, but…
off the top of my head, it looks like the join condition involves two columns, so there should be an index which includes both columns, although it’s not clear which should be the leading column
please let me know if you need an explanation for this