MySQL Forums
Forum List  »  InnoDB

Re: update queries running slow after colation change
Posted by: Peter Brawley
Date: April 26, 2020 07:07PM

To find the columns responsible for the slowdown, for those tables now updating slowly, I'd create table copies using old data and the old DDL, write an sproc that benchmarks updates on old & new tables and spits out the worst time diffs.

The sproc would, for each table x ...

(i) create a table x_old with old data that's never been through the port to innodb

(ii) from information_schema or Show Table specs, generate update queries that run long enough to show a time diff if it exists (presumably such updates either use other tables & columns, or input of literals ... either can create collation difficulties).

(iii) output those results

For such columns, develop cast() rewrites that improve performance, see http://mysql.rjweb.org/doc.php/charcoll for hints on doing that.

Sorry, now you see why it's often called charset hell.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: update queries running slow after colation change
92
April 26, 2020 07:07PM


Sorry, only registered users may post in this forum.

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.