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

[tothewine]

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...

[yuri]

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.

[yuri]

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?

[tothewine]

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?

[yuri]

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?

[tothewine]

I tested it works good.

[tothewine]

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.

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.