Tweet
It takes around 10 secs to update a user on Task entity. On investingation it seems the major roadblock is processNoteACL of Service.php.
The corresponding query is
SELECT note.id AS `id`, note.parent_type AS `parentType`, note.parent_id AS `parentId`, note.super_parent_type AS `superParentType`, note.super_parent_id AS `superParentId`, note.is_internal AS `isInternal`, note.related_type AS `relatedType`, note.related_id AS `relatedId`, note.created_at AS `createdAt` FROM `note` AS `note` WHERE ((note.related_id = '660c55263d4a29af9' AND note.related_type = 'Task') OR (note.parent_id = '660c55263d4a29af9' AND note.parent_type = 'Task' AND note.super_parent_id IS NOT NULL AND note.related_id IS NULL)) AND note.deleted = 0 ORDER BY note.number DESC LIMIT 0, 50;
The index used here is note.number (unique index) resulting in tablescan. Removing the same result in use of index on related_id. But it appears since there is a condition to check related_id is NULL in the OR clause, - the performance on index is less. On removing the note.related_id IS NULL , two separate indexes are used- one one related_id and another on parent_id and the query is super quick.
Any suggestion to improve this or make is asynchronous. Is it safe to remove related_is IS NULL on the OR clause.
The corresponding query is
SELECT note.id AS `id`, note.parent_type AS `parentType`, note.parent_id AS `parentId`, note.super_parent_type AS `superParentType`, note.super_parent_id AS `superParentId`, note.is_internal AS `isInternal`, note.related_type AS `relatedType`, note.related_id AS `relatedId`, note.created_at AS `createdAt` FROM `note` AS `note` WHERE ((note.related_id = '660c55263d4a29af9' AND note.related_type = 'Task') OR (note.parent_id = '660c55263d4a29af9' AND note.parent_type = 'Task' AND note.super_parent_id IS NOT NULL AND note.related_id IS NULL)) AND note.deleted = 0 ORDER BY note.number DESC LIMIT 0, 50;
The index used here is note.number (unique index) resulting in tablescan. Removing the same result in use of index on related_id. But it appears since there is a condition to check related_id is NULL in the OR clause, - the performance on index is less. On removing the note.related_id IS NULL , two separate indexes are used- one one related_id and another on parent_id and the query is super quick.
Any suggestion to improve this or make is asynchronous. Is it safe to remove related_is IS NULL on the OR clause.
Comment