Problem with:
Code:
USE INDEX (`IDX_DATE_SENT`)
And here is the fix (added by default at Espo CRM 9):
SELECT
email.id AS `id`,
email.name AS `name`,
email.deleted AS `deleted`,
email.from_string AS `fromString`,
email.reply_to_string AS `replyToString`,
email.address_name_map AS `addressNameMap`,
email.is_replied AS `isReplied`,
email.message_id AS `messageId`,
email.message_id_internal AS `messageIdInternal`,
email.body_plain AS `bodyPlain`,
email.body AS `body`,
email.is_html AS `isHtml`,
email.status AS `status`,
email.has_attachment AS `hasAttachment`,
email.date_sent AS `dateSent`,
email.delivery_date AS `deliveryDate`,
email.created_at AS `createdAt`,
email.modified_at AS `modifiedAt`,
email.is_system AS `isSystem`,
email.ics_contents AS `icsContents`,
email.ics_event_uid AS `icsEventUid`,
email.from_email_address_id AS `fromEmailAddressId`,
fromEmailAddress.name AS `fromEmailAddressName`,
email.parent_id AS `parentId`,
email.parent_type AS `parentType`,
email.created_by_id AS `createdById`,
NULLIF(
TRIM(
CONCAT(
IFNULL(createdBy.first_name, ''),
' ',
IFNULL(createdBy.last_name, '')
)
),
''
) AS `createdByName`,
email.sent_by_id AS `sentById`,
NULLIF(
TRIM(
CONCAT(
IFNULL(sentBy.first_name, ''),
' ',
IFNULL(sentBy.last_name, '')
)
),
''
) AS `sentByName`,
email.modified_by_id AS `modifiedById`,
NULLIF(
TRIM(
CONCAT(
IFNULL(modifiedBy.first_name, ''),
' ',
IFNULL(modifiedBy.last_name, '')
)
),
''
) AS `modifiedByName`,
email.assigned_user_id AS `assignedUserId`,
NULLIF(
TRIM(
CONCAT(
IFNULL(assignedUser.first_name, ''),
' ',
IFNULL(assignedUser.last_name, '')
)
),
''
) AS `assignedUserName`,
email.replied_id AS `repliedId`,
replied.name AS `repliedName`,
email.created_event_id AS `createdEventId`,
email.created_event_type AS `createdEventType`,
email.group_folder_id AS `groupFolderId`,
groupFolder.name AS `groupFolderName`,
email.account_id AS `accountId`,
account.name AS `accountName`,
emailUser.is_read AS `isRead`,
emailUser.is_important AS `isImportant`,
emailUser.in_trash AS `inTrash`,
emailUser.folder_id AS `folderId`
FROM
`email` AS `email` USE INDEX (`IDX_DATE_SENT`)
LEFT JOIN `account` AS `account` ON email.account_id = account.id
LEFT JOIN `group_email_folder` AS `groupFolder` ON email.group_folder_id = groupFolder.id
LEFT JOIN `email_address` AS `fromEmailAddress` ON email.from_email_address_id = fromEmailAddress.id
LEFT JOIN `email` AS `replied` ON email.replied_id = replied.id
LEFT JOIN `user` AS `sentBy` ON email.sent_by_id = sentBy.id
LEFT JOIN `user` AS `assignedUser` ON email.assigned_user_id = assignedUser.id
LEFT JOIN `user` AS `modifiedBy` ON email.modified_by_id = modifiedBy.id
LEFT JOIN `user` AS `createdBy` ON email.created_by_id = createdBy.id
LEFT JOIN `email_user` AS `emailUser` ON emailUser.email_id = email.id
AND emailUser.deleted = 0
AND emailUser.user_id = '1'
WHERE
email.id = '65211b495413eb143'
AND email.deleted = 0
ORDER BY
email.date_sent DESC,
email.id DESC
LIMIT
0, 1
USE INDEX (`IDX_DATE_SENT`)
# Query_time: 14.541477 Lock_time: 0.000011 Rows_sent: 1 Rows_examined: 221855 use swp_espocrm; SET timestamp=1697506170; SELECT email.id AS `id`, email.name AS `name`, email.deleted AS `deleted`, email.from_string AS `fromString`, email.reply_to_string AS `replyToString`, email.address_name_map AS `addressNameMap`, email.is_replied AS `isReplied`, email.message_id AS `messageId`, email.message_id_internal AS `messageIdInternal`, email.body_plain AS `bodyPlain`, email.body AS `body`, email.is_html AS `isHtml`, email.status AS `status`, email.has_attachment AS `hasAttachment`, email.date_sent AS `dateSent`, email.delivery_date AS `deliveryDate`, email.created_at AS `createdAt`, email.modified_at AS `modifiedAt`, email.is_system AS `isSystem`, email.ics_contents AS `icsContents`, email.ics_event_uid AS `icsEventUid`, email.from_email_address_id AS `fromEmailAddressId`, fromEmailAddress.name AS `fromEmailAddressName`, email.parent_id AS `parentId`, email.parent_type AS `parentType`, email.created_by_id AS `createdById`, NULLIF(TRIM(CONCAT(IFNULL(createdBy.first_name, ''), ' ', IFNULL(createdBy.last_name, ''))), '') AS `createdByName`, email.sent_by_id AS `sentById`, NULLIF(TRIM(CONCAT(IFNULL(sentBy.first_name, ''), ' ', IFNULL(sentBy.last_name, ''))), '') AS `sentByName`, email.modified_by_id AS `modifiedById`, NULLIF(TRIM(CONCAT(IFNULL(modifiedBy.first_name, ''), ' ', IFNULL(modifiedBy.last_name, ''))), '') AS `modifiedByName`, email.assigned_user_id AS `assignedUserId`, NULLIF(TRIM(CONCAT(IFNULL(assignedUser.first_name, ''), ' ', IFNULL(assignedUser.last_name, ''))), '') AS `assignedUserName`, email.replied_id AS `repliedId`, replied.name AS `repliedName`, email.created_event_id AS `createdEventId`, email.created_event_type AS `createdEventType`, email.group_folder_id AS `groupFolderId`, groupFolder.name AS `groupFolderName`, email.account_id AS `accountId`, account.name AS `accountName`, emailUser.is_read AS `isRead`, emailUser.is_important AS `isImportant`, emailUser.in_trash AS `inTrash`, emailUser.folder_id AS `folderId` FROM `email` AS `email` USE INDEX (`IDX_DATE_SENT`) LEFT JOIN `account` AS `account` ON email.account_id = account.id LEFT JOIN `group_email_folder` AS `groupFolder` ON email.group_folder_id = groupFolder.id LEFT JOIN `email_address` AS `fromEmailAddress` ON email.from_email_address_id = fromEmailAddress.id LEFT JOIN `email` AS `replied` ON email.replied_id = replied.id LEFT JOIN `user` AS `sentBy` ON email.sent_by_id = sentBy.id LEFT JOIN `user` AS `assignedUser` ON email.assigned_user_id = assignedUser.id LEFT JOIN `user` AS `modifiedBy` ON email.modified_by_id = modifiedBy.id LEFT JOIN `user` AS `createdBy` ON email.created_by_id = createdBy.id LEFT JOIN `email_user` AS `emailUser` ON emailUser.email_id = email.id AND emailUser.deleted = 0 AND emailUser.user_id = '1' WHERE email.id = '65187eb0cfacda24f' AND email.deleted = 0 ORDER BY email.date_sent DESC, email.id DESC LIMIT 0, 1;
[mysqld] innodb_buffer_pool_size = 6G innodb_log_file_size = 256M
ANALYZE TABLE email, account, group_email_folder, email_address, user, email_user;
Leave a comment: