Announcement

Collapse
No announcement yet.

Email list view takes a lot of time for regular users...

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

  • Email list view takes a lot of time for regular users...

    This request takes 6 seconds with admin account but almost 2 minutes for regular users.

    /api/v1/Email?folderId=inbox&select=personStringData%2Csub ject%2CparentId%2CparentType%2CparentName%2CdateSe nt&maxSize=5&offset=0&orderBy=dateSent&order=des c

    I wanted to change the default sorting to use id or createdAt (as a workaround) but after changing entitydef and clearing cache the orderBy parameter is still dateSent...
    Last edited by tothewine; 11-11-2019, 10:33 PM.

  • #2
    I recommend to obtain SQL query and figure out what exactly causes this problem. https://github.com/espocrm/espocrm/b.../Base.php#L438

    Order by id or createdAt won't solve the problem.
    Last edited by yurikuzn; 11-11-2019, 09:36 PM.

    Comment


    • #3
      FYI. In our instance, email list view, 20 records per page

      for admin - less than 1 second
      team access restriction - 2 seconds

      MySQL 8.

      How much records in email and entity_team tables?

      Comment


      • #4
        I am using mysql 5.7. Below is the user query. At first I was thinking it was the ACL check but if comment the -- USE INDEX (`IDX_DATE_SENT`) part it works fast (0.4135 seconds). The email table contains 67554 rows. I think the problem is the index, but I tried already to drop it and recreate, and also to drop and rebuild. Is there some way to prevent that part to end up in the query? Can I suppress an index from metadata?

        I think it may be because the table is big and indexing it becomes less efficient than traversing it, somehow.

        SELECT
        DISTINCT email.id AS `id`,
        email.assigned_user_id AS `assignedUserId`,
        email.created_by_id AS `createdById`,
        email.parent_id AS `parentId`,
        email.parent_type AS `parentType`,
        email.date_sent AS `dateSent`,
        email.name AS `name`,
        email.from_string AS `fromString`,
        email.from_email_address_id AS `fromEmailAddressId`,
        fromEmailAddress.name AS `fromEmailAddressName`,
        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`,
        usersMiddle.is_read AS `isRead`,
        usersMiddle.is_important AS `isImportant`,
        usersMiddle.in_trash AS `inTrash`,
        usersMiddle.folder_id AS `folderId`
        FROM
        `email` USE INDEX (`IDX_DATE_SENT`)
        LEFT JOIN `email_address` AS `fromEmailAddress` ON email.from_email_address_id = fromEmailAddress.id
        LEFT JOIN `entity_team` AS `teamsAccessMiddle` ON email.id = teamsAccessMiddle.entity_id
        AND teamsAccessMiddle.deleted = '0'
        AND teamsAccessMiddle.entity_type = 'Email'
        LEFT JOIN `team` AS `teamsAccess` ON teamsAccess.id = teamsAccessMiddle.team_id
        AND teamsAccess.deleted = '0'
        LEFT JOIN `email_user` AS `usersMiddle` ON email.id = usersMiddle.email_id
        AND usersMiddle.deleted = '0'
        AND usersMiddle.user_id = '5c35d008280946313'
        LEFT JOIN `user` AS `users` ON users.id = usersMiddle.user_id
        AND users.deleted = '0'
        WHERE
        (
        (
        teamsAccessMiddle.team_id IN ('5a4e911d0849fcc17')
        OR usersMiddle.user_id = '5c35d008280946313'
        )
        )
        AND (
        usersMiddle.in_trash = ''
        AND usersMiddle.folder_id IS NULL
        AND (email.status IN ('Archived', 'Sent'))
        AND email.from_email_address_id NOT IN ('5c35cf61d3b6fefb3')
        AND (
        (
        email.status = 'Archived'
        OR email.created_by_id <> '5c35d008280946313'
        )
        )
        )
        AND (usersMiddle.user_id = '5c35d008280946313')
        AND email.deleted = '0'
        ORDER BY
        email.date_sent DESC
        LIMIT
        0, 6

        For now I am editing core metadata to delete the indexes on dateSent.

        I think this issue will probably boil down to two new features possibly:
        - some heuristic to automatically choose if index should be explicitly used based on some conditions (like big table for example)
        - ability to explicitly suppress arbitrary indexes from custom metadata and prevent them to be created during rebuild

        ps. Does the sortBy value in metadata refer to a column or is the name of an index?




        Last edited by tothewine; 11-11-2019, 10:47 PM.

        Comment


        • #5
          Here's where the index is applied https://github.com/espocrm/espocrm/b.../Email.php#L78

          On MySQL 8 it's beneficial to use this index when ACL restrictions are applied to the query. On MySQL 5.7 it appears to be not. Could you comment out the line and test both for admin and user?
          Last edited by yurikuzn; 11-12-2019, 06:55 AM.

          Comment


          • #6
            I tested it works good.

            Comment


            • #7
              I think I'm having a slow query problem when ordering Leads by a custom Date field.
              If I remove the ORDER BY part from the query it is fast. The resulting query is below.
              So I added an index for that field in metadata, rebuilt but it's still slow.

              Code:
              SELECT DISTINCT
                  lead.id AS `id`,
                  lead.assigned_user_id AS `assignedUserId`,
                  lead.created_by_id AS `createdById`,
                  lead.account_name AS `accountName`,
                  lead.partita_i_v_a AS `partitaIVA`,
                  emailAddresses.opt_out AS `emailAddressIsOptedOut`,
                  emailAddresses.name AS `emailAddress`,
                  lead.address_postal_code AS `addressPostalCode`,
                  phoneNumbers.opt_out AS `phoneNumberIsOptedOut`,
                  phoneNumbers.name AS `phoneNumber`,
                  lead.address_city AS `addressCity`,
                  lead.status AS `status`,
                  lead.last_call AS `lastCall`,
                  lead.modified_at AS `modifiedAt`,
                  lead.created_at AS `createdAt`
              FROM
                  `lead`
              LEFT JOIN `entity_team` AS `teamsAccessMiddle`
              ON
                  lead.id = teamsAccessMiddle.entity_id AND teamsAccessMiddle.deleted = '0' AND teamsAccessMiddle.entity_type = 'Lead'
              LEFT JOIN `team` AS `teamsAccess`
              ON
                  teamsAccess.id = teamsAccessMiddle.team_id AND teamsAccess.deleted = '0'
              LEFT JOIN `entity_email_address` AS `emailAddressesMiddle`
              ON
                  lead.id = emailAddressesMiddle.entity_id AND emailAddressesMiddle.deleted = '0' AND emailAddressesMiddle.primary = '1' AND emailAddressesMiddle.entity_type = 'Lead'
              LEFT JOIN `email_address` AS `emailAddresses`
              ON
                  emailAddresses.id = emailAddressesMiddle.email_address_id AND emailAddresses.deleted = '0'
              LEFT JOIN `entity_phone_number` AS `phoneNumbersMiddle`
              ON
                  lead.id = phoneNumbersMiddle.entity_id AND phoneNumbersMiddle.deleted = '0' AND phoneNumbersMiddle.primary = '1' AND phoneNumbersMiddle.entity_type = 'Lead'
              LEFT JOIN `phone_number` AS `phoneNumbers`
              ON
                  phoneNumbers.id = phoneNumbersMiddle.phone_number_id AND phoneNumbers.deleted = '0'
              WHERE
                  (
                      (
                          teamsAccess.id IN('5a0d9fb4ca099923d') OR lead.assigned_user_id = '5ba31a7452b19d9f2'
                      )
                  ) AND lead.deleted = '0'
              ORDER BY
                  lead.last_call ASC
              LIMIT 0, 50
              Also, the index shows very 390 cardinality over 80k records so I think it isn't used.
              Last edited by tothewine; 11-16-2019, 09:44 AM.

              Comment


              • #8
                What can I do for Leads table? I'm starting to think this slowness could be due to disk I/O. I want to exclude that possibility somehow.

                Ps.. Regardless, an interesting approach to ACL is described here and could improve performance: https://www.xaprb.com/blog/2006/08/1...ontrol-in-sql/
                Last edited by tothewine; 11-20-2019, 01:33 AM.

                Comment

                Working...
                X