Very very slow and high mysql cpu usage

[salvalerma]

Hi, after upgrading from 5.9 to 7.2.7, I have a performance issue when executing updates from the api. Before it could run dozens of updates per second. It now takes several seconds to update a record. It takes mysql to 100% cpu usage.

Looking at the mysql logs, there is a query that takes an average of 5 seconds:

"SELECT note.id AS `id`,
note.parent_type AS `parentType`,
note.parent_id AS `parentId`,
note.super_parent_type AS `superParentType`,
note.super_parent_id AS `superParentId`,
note.is_internal AS `isInternal`,
note.related_type AS `relatedType`,
note.related_id AS `relatedId`,
note.created_at AS `createdAt`
FROM `note`
WHERE ((note.related_id = 'S' AND note.related_type = 'S') OR
(note.parent_id = 'S' AND note.parent_type = 'S' AND note.super_parent_id IS NOT NULL AND
note.related_id IS NULL))
AND note.deleted = N
ORDER BY note.number DESC
LIMIT N, N"

I have come across several problems that I have been solving, such as that now the read-only fields cannot be updated through the api. To fix this I have changed all the read-only fields that I had created to allow update.

Can you help me?

Thank you.

[MaxDau]

How many notes are in the table?

[salvalerma]

1056284 rows.

[MaxDau]

And how many of them have deleted=1?
Are all requests slower or is it just the one request?

Have you tried creating an index for your query?
(note.parent_id = 'S' AND note.parent_type = 'S' AND note.super_parent_id IS NOT NULL AND note.related_id IS NULL)

[item]

Hi,

look here :

https://forum.espocrm.com/forum/installation-and-upgrade-help/60558-id-collation-changed-after-upgrade#post61212