Announcement

Collapse
No announcement yet.

Slow opening of the letter

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Slow opening of the letter

    When opening a letter, the following request is generated:
    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;​
    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

  • #2
    I always ask more details for posts in bug reports section. And steps to reproduce.

    What database system? MariaDB, MySQL? Which version. They perform differently. More context required.

    It's difficult to figure out what exactly generates this query. I could not reproduce. More details would be helpful. It looks to me that it's from the History panel. But I'm not sure. Or maybe it's a list view with a filter.

    I don't know what caused these parts added to the query:

    * (email.parent_id = '65c0b1b69a8194962') AND (email.parent_type = 'Case')
    * LIMIT 0, 2
    Last edited by yuri; 02-12-2024, 09:56 AM.

    Comment


    • #3
      When opening email detail view. (/#Email/view/65c9706359c9aa839) - api request /api/v1/Email/65c9706359c9aa839

      Code:
      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.reply_to AS `replyTo`,
      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 = '102'
      WHERE email.id = '65c9706359c9aa839'
      AND email.deleted = 0
      ORDER BY email.date_sent DESC, email.id DESC
      LIMIT 0, 1​
      Additional - clientDef, entityDef, scope for email entity from meta on screenshots
      Attached Files

      Comment


      • #4
        This is a completely different query than in the initial post. Should I neglect the initial query?

        This fix by-passes index for the read request: https://github.com/espocrm/espocrm/c...aee6cfe6520f13.
        Last edited by yuri; 02-12-2024, 10:22 AM.

        Comment


        • #5
          On what database system you get the performance drop?

          Comment


          • #6
            mariadb 10.6.16

            I try find where previous request. appear. I see slow queries in database log after user requests.

            Comment


            • esforim
              esforim commented
              Editing a comment
              This sound like my issue too! Thanks for the report dimyy, hopefully you and yuri can diagnose and I just smile at next update.

          • #7
            I added monitoring of all slow queries. I collect information to accurately determine the location of requests and duration. Not only from the query log in the database
            Attached Files

            Comment

            Working...
            X