Tweet
When opening a letter, the following request is generated:
Forcing the use of an index (USE INDEX (`IDX_DATE_SENT`)) in this context is erroneous and greatly slows things down.
Without this line, the request is processed almost instantly
758 ms vs 8 s 190 ms
Code:
SELECT email.assigned_user_id AS `assignedUserId`,
email.id AS `id`,
email.parent_id AS `parentId`,
email.name AS `name`,
email.created_by_id AS `createdById`,
email.date_sent AS `dateSent`,
email.from_string AS `fromString`,
email.from_email_address_id AS `fromEmailAddressId`,
fromEmailAddress.name AS `fromEmailAddressName`,
email.parent_type AS `parentType`,
email.is_html AS `isHtml`,
email.is_replied AS `isReplied`,
email.status AS `status`,
email.account_id AS `accountId`,
email.message_id AS `messageId`,
email.sent_by_id AS `sentById`,
email.reply_to_string AS `replyToString`,
email.has_attachment AS `hasAttachment`,
email.group_folder_id AS `groupFolderId`,
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 `email_address` AS `fromEmailAddress` ON email.from_email_address_id = fromEmailAddress.id
LEFT JOIN `email_user` AS `emailUser` ON emailUser.email_id = email.id AND emailUser.deleted = 0 AND
emailUser.user_id = '615ce8d3adcc3267d'
WHERE (((email.parent_id = '65c0b1b69a8194962') AND (email.parent_type = 'Case')))
AND (emailUser.in_trash = 0 AND emailUser.folder_id IS NULL AND emailUser.user_id = '615ce8d3adcc3267d' AND
(email.status IN ('Archived', 'Sent') AND email.group_folder_id IS NULL) AND
(email.status = 'Archived' AND email.created_by_id <> '615ce8d3adcc3267d'))
AND email.deleted = 0
ORDER BY email.date_sent DESC, email.id DESC
LIMIT 0, 2;
Without this line, the request is processed almost instantly
758 ms vs 8 s 190 ms
Comment