EntityTeam indexes

[a.slyzhko]

Hi there!
I can't figure out what is the proper way to create indexes in EntityTeam table. Due to our new business logic we rely on this table heavily, and queries are tremendously slow. I tried to applied proper indexes manually, but and I was satisfied with result, but I know that after rebuild, those indexes will be gone.
Any help will be very much appreciated!

[yuri]

Hello,

Did you add custom columns to the entity_team table?

[a.slyzhko]

No, I didn't. But I lack these indexes

CREATE INDEX IDX_ENTITY_ID_ENTITY_TYPE_DELETED ON `entity_team` (entity_id, entity_type, deleted);
CREATE INDEX IDX_TEAM_ID_ENTITY_TYPE_DELETED ON `entity_team` (team_id, entity_type, deleted); 

[yuri]

A custom converter for the entityTeams relationship can be defined.

Docs: https://docs.espocrm.com/development...relationships/
Core example: https://github.com/espocrm/espocrm/b...nships.json#L9

Then, in the converter you can add the "indexes" parameter.

https://docs.espocrm.com/development/metadata/entity-defs/#indexes

[yuri]

Could you provide examples in what cases these indexes are necessary? Is there any out-of-the-box logic that could be improved with these indexes?

When I did tests on different databases, MariaDB and PostgreSQL outperformed MySQL by a huge magnitude when dealing with filtering by related teams.

[yuri]

Do you have the Cleanup job running? Maybe you have the large number of deleted = true records in the table.

[a.slyzhko]

I implemented functionality, which allows users from team A see records of team B, C D, etc. These rules are encapsulated in new custom entity. And when user have acl read level set to team, these custom rules are activated and custom access control filter is applied. When listing records, I made a couple of joins with entity_team, so I end up with this sql generated:

SELECT account.id                 AS `id`,
       account.assigned_user_id   AS `assignedUserId`,
       account.created_by_id      AS `createdById`,
       account.created_at         AS `createdAt`,
       account.name               AS `name`
FROM `account`
WHERE account.id IN (SELECT account.id AS `id`
                     FROM `account`
                              LEFT JOIN `entity_team` AS `accountTeam` ON (accountTeam.entity_id = account.id AND
                                                                           accountTeam.entity_type = 'Account' AND
                                                                           accountTeam.deleted = 0)
                              LEFT JOIN `entity_team` AS `accountTeamAccessTeam`
                                        ON (accountTeamAccessTeam.team_id = accountTeam.team_id AND
                                            accountTeamAccessTeam.entity_type = 'ATAAccountTeamAccess' AND
                                            accountTeamAccessTeam.deleted = 0)
                              LEFT JOIN `a_t_a_account_team_access` AS `accountTeamAccess`
                                        ON (accountTeamAccess.id = accountTeamAccessTeam.entity_id AND
                                            accountTeamAccess.deleted = 0)
                     WHERE ((account.assigned_user_id = '655fa0141dd055c9a') OR
                            (accountTeam.team_id IN ('654ba8a4919056a41')) OR
                            (accountTeamAccess.team_id IN ('654ba8a4919056a41')))
                       AND account.deleted = 0)
  AND account.deleted = 0
ORDER BY account.created_at DESC, account.id DESC
LIMIT 0, 20; 

This exact query takes about 20s to execute without indexes, and nearly instantly shows result set after applying indexes.

P.S: forgot to say, that I use the latest LTS MariaDB 11.4

[a.slyzhko]

Do you have the Cleanup job running? Maybe you have the large number of deleted = true records in the table.

Only 240 records deleted, and 257k with deleted = false