EspoCRM stalls every few minutes

[livewire]

Hi

Im using Espo 7.3. In the past few days i have been getting timeout errors and error 500 when reading or viewing leads. On my error log i see the below error like every few minutes

ERROR: Uncaught Exception PDOException: "SQLSTATE[HY000] [2002] Connection refused

when i contacted my web host they said that some queries are taking more than 30 seconds and should be optimized.

(
SELECT note.id AS `id`, note.number AS `number`, note.type AS `type`, note.post AS `post`, note.data AS `data`, note.parent_type AS `parentType`, note.parent_id AS `parentId`, note.related_type AS `relatedType`, note.related_id AS `relatedId`, note.target_type AS `targetType`, note.created_at AS `createdAt`, note.created_by_id AS `createdById`, NULLIF(TRIM(CONCAT(IFNULL(createdBy.first_name, ''), ' ', IFNULL(createdBy.last_name, ''))), '') AS `createdByName`, note.is_global AS `isGlobal`, note.is_internal AS `isInternal`, createdBy.gender AS `createdByGender` FROM `note` USE INDEX (`UNIQ_NUMBER`) JOIN `subscription` AS `subscription` ON subscription.entity_type = note.parent_type AND subscription.entity_id = note.parent_id AND subscription.user_id = '63c4fdbcd41821b5d' LEFT JOIN `user` AS `createdBy` ON note.created_by_id = createdBy.id WHERE (((note.related_id IS NOT NULL AND note.related_type NOT IN ('Call','Contact','Lead','Task')) OR (note.related_id IS NULL))) AND (((note.related_type IS NULL OR note.related_type NOT IN ('Email','User','Account','Campaign','Case','Docum ent','KnowledgeBaseArticle','Meeting','Opportunity ','TargetList'))) AND ((note.parent_type IS NULL OR note.parent_type NOT IN ('Email','User','Account','Campaign','Case','Docum ent','KnowledgeBaseArticle','Meeting','Opportunity ','TargetList'))) AND (note.type NOT IN ('EmailReceived','EmailSent'))) AND (note.created_by_id <> '63c4fdbcd41821b5d') AND note.deleted = '0' ORDER BY note.number DESC LIMIT 0, 11
)
UNION ALL
(
SELECT DISTINCT note.id AS `id`, note.number AS `number`, note.type AS `type`, note.post AS `post`, note.data AS `data`, note.parent_type AS `parentType`, note.parent_id AS `parentId`, note.related_type AS `relatedType`, note.related_id AS `relatedId`, note.target_type AS `targetType`, note.created_at AS `createdAt`, note.created_by_id AS `createdById`, NULLIF(TRIM(CONCAT(IFNULL(createdBy.first_name, ''), ' ', IFNULL(createdBy.last_name, ''))), '') AS `createdByName`, note.is_global AS `isGlobal`, note.is_internal AS `isInternal`, createdBy.gender AS `createdByGender` FROM `note` USE INDEX (`UNIQ_NUMBER`) JOIN `subscription` AS `subscription` ON subscription.entity_type = note.parent_type AND subscription.entity_id = note.parent_id AND subscription.user_id = '63c4fdbcd41821b5d' LEFT JOIN `user` AS `createdBy` ON note.created_by_id = createdBy.id LEFT JOIN `note_user` AS `noteUser` ON noteUser.note_id = note.id AND noteUser.deleted = '' WHERE ((note.related_id IS NOT NULL AND note.related_type IN ('Call','Contact','Lead','Task')) AND noteUser.user_id = '63c4fdbcd41821b5d') AND (((note.related_type IS NULL OR note.related_type NOT IN ('Email','User','Account','Campaign','Case','Docum ent','KnowledgeBaseArticle','Meeting','Opportunity ','TargetList'))) AND ((note.parent_type IS NULL OR note.parent_type NOT IN ('Email','User','Account','Campaign','Case','Docum ent','KnowledgeBaseArticle','Meeting','Opportunity ','TargetList'))) AND (note.type NOT IN ('EmailReceived','EmailSent'))) AND (note.created_by_id <> '63c4fdbcd41821b5d') AND note.deleted = '0' ORDER BY note.number DESC LIMIT 0, 11
)
UNION ALL
(
SELECT note.id AS `id`, note.number AS `number`, note.type AS `type`, note.post AS `post`, note.data AS `data`, note.parent_type AS `parentType`, note.parent_id AS `parentId`, note.related_type AS `relatedType`, note.related_id AS `relatedId`, note.target_type AS `targetType`, note.created_at AS `createdAt`, note.created_by_id AS `createdById`, NULLIF(TRIM(CONCAT(IFNULL(createdBy.first_name, ''), ' ', IFNULL(createdBy.last_name, ''))), '') AS `createdByName`, note.is_global AS `isGlobal`, note.is_internal AS `isInternal`, createdBy.gender AS `createdByGender` FROM `note` USE INDEX (`UNIQ_NUMBER`) JOIN `subscription` AS `subscription` ON subscription.entity_type = note.super_parent_type AND subscription.entity_id = note.super_parent_id AND subscription.user_id = '63c4fdbcd41821b5d' LEFT JOIN `user` AS `createdBy` ON note.created_by_id = createdBy.id WHERE ((note.parent_id <> note.super_parent_id OR note.parent_type <> note.super_parent_type)) AND (((note.related_id IS NOT NULL AND note.related_type NOT IN ('Call','Contact','Lead','Task')) OR (note.related_id IS NULL AND note.parent_type NOT IN ('Call','Contact','Lead','Task')))) AND (((note.related_type IS NULL OR note.related_type NOT IN ('Email','User','Account','Campaign','Case','Docum ent','KnowledgeBaseArticle','Meeting','Opportunity ','TargetList'))) AND ((note.parent_type IS NULL OR note.parent_type NOT IN ('Email','User','Account','Campaign','Case','Docum ent','KnowledgeBaseArticle','Meeting','Opportunity ','TargetList'))) AND (note.type NOT IN ('EmailReceived','EmailSent'))) AND (note.created_by_id <> '63c4fdbcd41821b5d') AND note.deleted = '0' ORDER BY note.number DESC LIMIT 0, 11
)
UNION ALL
(
SELECT DISTINCT note.id AS `id`, note.number AS `number`, note.type AS `type`, note.post AS `post`, note.data AS `data`, note.parent_type AS `parentType`, note.parent_id AS `parentId`, note.related_type AS `relatedType`, note.related_id AS `relatedId`, note.target_type AS `targetType`, note.created_at AS `createdAt`, note.created_by_id AS `createdById`, NULLIF(TRIM(CONCAT(IFNULL(createdBy.first_name, ''), ' ', IFNULL(createdBy.last_name, ''))), '') AS `createdByName`, note.is_global AS `isGlobal`, note.is_internal AS `isInternal`, createdBy.gender AS `createdByGender` FROM `note` USE INDEX (`UNIQ_NUMBER`) JOIN `subscription` AS `subscription` ON subscription.entity_type = note.super_parent_type AND subscription.entity_id = note.super_parent_id AND subscription.user_id = '63c4fdbcd41821b5d' LEFT JOIN `user` AS `createdBy` ON note.created_by_id = createdBy.id LEFT JOIN `note_user` AS `noteUser` ON noteUser.note_id = note.id AND noteUser.deleted = '' WHERE ((note.parent_id <> note.super_parent_id OR note.parent_type <> note.super_parent_type)) AND (((note.related_id IS NOT NULL AND note.related_type IN ('Call','Contact','Lead','Task')) OR (note.related_id IS NULL AND note.parent_type IN ('Call','Contact','Lead','Task'))) AND noteUser.user_id = '63c4fdbcd41821b5d') AND (((note.related_type IS NULL OR note.related_type NOT IN ('Email','User','Account','Campaign','Case','Docum ent','KnowledgeBaseArticle','Meeting','Opportunity ','TargetList'))) AND ((note.parent_type IS NULL OR note.parent_type NOT IN ('Email','User','Account','Campaign','Case','Docum ent','KnowledgeBaseArticle','Meeting','Opportunity ','TargetList'))) AND (note.type NOT IN ('EmailReceived','EmailSent'))) AND (note.created_by_id <> '63c4fdbcd41821b5d') AND note.deleted = '0' ORDER BY note.number DESC LIMIT 0, 11
)
UNION ALL
(
SELECT note.id AS `id`, note.number AS `number`, note.type AS `type`, note.post AS `post`, note.data AS `data`, note.parent_type AS `parentType`, note.parent_id AS `parentId`, note.related_type AS `relatedType`, note.related_id AS `relatedId`, note.target_type AS `targetType`, note.created_at AS `createdAt`, note.created_by_id AS `createdById`, NULLIF(TRIM(CONCAT(IFNULL(createdBy.first_name, ''), ' ', IFNULL(createdBy.last_name, ''))), '') AS `createdByName`, note.is_global AS `isGlobal`, note.is_internal AS `isInternal`, createdBy.gender AS `createdByGender` FROM `note` LEFT JOIN `user` AS `createdBy` ON note.created_by_id = createdBy.id WHERE note.created_by_id = '63c4fdbcd41821b5d' AND note.parent_id IS NULL AND note.type = 'Post' AND note.is_global = '' AND (((note.related_type IS NULL OR note.related_type NOT IN ('Email','User','Account','Campaign','Case','Docum ent','KnowledgeBaseArticle','Meeting','Opportunity ','TargetList'))) AND ((note.parent_type IS NULL OR note.parent_type NOT IN ('Email','User','Account','Campaign','Case','Docum ent','KnowledgeBaseArticle','Meeting','Opportunity ','TargetList'))) AND (note.type NOT IN ('EmailReceived','EmailSent'))) AND note.deleted = '0' ORDER BY note.number DESC LIMIT 0, 11
)
UNION ALL
(
SELECT note.id AS `id`, note.number AS `number`, note.type AS `type`, note.post AS `post`, note.data AS `data`, note.parent_type AS `parentType`, note.parent_id AS `parentId`, note.related_type AS `relatedType`, note.related_id AS `relatedId`, note.target_type AS `targetType`, note.created_at AS `createdAt`, note.created_by_id AS `createdById`, NULLIF(TRIM(CONCAT(IFNULL(createdBy.first_name, ''), ' ', IFNULL(createdBy.last_name, ''))), '') AS `createdByName`, note.is_global AS `isGlobal`, note.is_internal AS `isInternal`, createdBy.gender AS `createdByGender` FROM `note` LEFT JOIN `note_user` AS `usersMiddle` ON note.id = usersMiddle.note_id AND usersMiddle.deleted = '0' LEFT JOIN `user` AS `users` ON users.id = usersMiddle.user_id AND users.deleted = '0' LEFT JOIN `user` AS `createdBy` ON note.created_by_id = createdBy.id WHERE note.created_by_id <> '63c4fdbcd41821b5d' AND usersMiddle.user_id = '63c4fdbcd41821b5d' AND note.parent_id IS NULL AND note.type = 'Post' AND note.is_global = '' AND (((note.related_type IS NULL OR note.related_type NOT IN ('Email','User','Account','Campaign','Case','Docum ent','KnowledgeBaseArticle','Meeting','Opportunity ','TargetList'))) AND ((note.parent_type IS NULL OR note.parent_type NOT IN ('Email','User','Account','Campaign','Case','Docum ent','KnowledgeBaseArticle','Meeting','Opportunity ','TargetList'))) AND (note.type NOT IN ('EmailReceived','EmailSent'))) AND (note.created_by_id <> '63c4fdbcd41821b5d') AND note.deleted = '0' ORDER BY note.number DESC LIMIT 0, 11
)

ORDER BY number DESC
LIMIT 0, 11;​

I just tried to search this query but couldn't find it.

Can someone let me know whats the best way to optimize this query?

Thanks

[lazovic]

Hi livewire, essaystay,

In order to get rid of this problem, we need to analyze your answers to the following questions:

1. What version of MySQL are you using?
2. Are there any errors in the EspoCRM error log?
3. Are there any errors in the server log?
4. How much free space is left on your server?