Optimize sql query update in MySQL 5.5.62 version

Hello guys,

I working with MySql RDBMS

mysql> SELECT VERSION();
+------------------+
| VERSION()        |
+------------------+
| 5.5.62           |
+------------------+
1 row in set

On my MySql RDBMS I have two tables

  1. tbl_old , number of rows 745.088
  2. 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

how to optimize this query execution?

thank you in advance for any help

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?

1 Like

thanks for reply

I have tried without case statements in WHERE clause

the situation don’t change

the execution of the query is very long time and I have to stopping the query

the problem is elsewhere

UPDATE `tbl_new` jjj,
 `tbl_old` kkk
SET jjj.`cod_New` = kkk.`cod_Old`
WHERE
    jjj.`COD_ORG_NEW` = kkk.`COD_ORG_OLD`
AND jjj.`element_New` = kkk.`element_Old`;

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 :wink:

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…

1 Like

nope, they’re the same

problem is missing indexes

@UncleVince could you do a SHOW CREATE TABLE for each table please, and copy the text back here (i.e. no screenshot please)

1 Like

Okay, thanks

CREATE TABLE `tbl_new` (
  `COD_ORG_NEW` char(4) DEFAULT NULL,
  `element_New` char(6) DEFAULT NULL,
  `cod_New` char(6) DEFAULT NULL,
  `TYPE_2` char(2) DEFAULT NULL,
  `sID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`sID`),
  KEY `COD_ORG_NEW` (`COD_ORG_NEW`),
  KEY `element_New` (`element_New`),
  KEY `cod_New` (`cod_New`),
  KEY `TYPE_2` (`TYPE_2`)
) ENGINE=MyISAM AUTO_INCREMENT=115128 DEFAULT CHARSET=latin1;



CREATE TABLE `tbl_old` (
  `COD_ORG_Old` char(4) DEFAULT NULL,
  `cod_Old` char(6) DEFAULT NULL,
  `cod_series` int(11) DEFAULT NULL,
  `element_Old` char(6) DEFAULT NULL,
  `sID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`sID`),
  KEY `COD_ORG_Old` (`COD_ORG_Old`),
  KEY `cod_Old` (`cod_Old`),
  KEY `cod_series` (`cod_series`),
  KEY `element_Old` (`element_Old`)
) ENGINE=MyISAM AUTO_INCREMENT=745089 DEFAULT CHARSET=latin1;

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

2 Likes
  1. I am not sure, but it could be a lot of NULL values in join columns. That cause the problem.

  2. What says EXPLAIN [your query]?

1 Like

thank you all for replies

solved from hosting by updating the MySql version from 5.5.62 to 8.0.21

now everything works well and as it should

1 Like

not to mention just in general benefits of being on an actual up to date version! :wink: Good to hear it.

1 Like