MySQL Forums
Forum List  »  InnoDB

update queries running slow after colation change
Posted by: Doug Barger
Date: April 24, 2020 10:26AM

I posted this in another thread here, but this is a better explanation.

I have a database that was using latin1_swedish_ci collation. I needed to preform Case sensitive joins. So I modified all the tables to use utf8mb4_0900_cs. The joins are across 2 different databases, and the collation is the same on both databases. My code was working find until I changed the collation on both databases. This code runs a series of dynamically created updates. The first few run relatively fast, but as it goes deeper into the run (more queries generated, approximately 15 will be generated depending on the table). it gets very slow. Eventually exceeding 30 seconds to run. Below is the typical sql query it generates. I am using odbc connectivity in I preformed an 'explain' and this was the result enter image description here
1 UPDATE a range pcdbpositionsPositionidx pcdbpositionsPositionidx 403 2 100.00 Using where
1 SIMPLE b index positionsPosition 102 5532 10.00 Using where; Not exists; Using index

typical update generated

update autoscratch.pass0 a
left join pcdb.positions b
on b.Position=a.pcdbpositionsPosition
set pcdbpositionsPosition = concat('(ERROR) ',pcdbpositionsPosition)
where b.Position is null and not a.pcdbpositionsPosition=''

Options: ReplyQuote

Written By
update queries running slow after colation change
April 24, 2020 10:26AM

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.