Potential performance issues in other items_items triggers

[GeoffreyHuck]

Where the issue was fixed

We fixed an issue when a relation between items is deleted in this PR: #1066

The problem was that more than 50k entries were added in results_propagate - in the before_delete_items_items trigger - when a relation in items_items was deleted (all the results with item_id=OLD.parent_item_id. This made the updateItem service timeout.

Other cases with the same issue that were not fixed

We have other triggers that add rows into results_propagate, like the before_delete_items_items used to:

• after_insert_items_items trigger: a row in results_propagate is added for each result with item_id=NEW.child_item_id. This happen if we add an item with many results as a child of another item.
• after_update_items_items: it's the same. So it'll happen if the propagation fields on items_items are updated, for each result with item_id=NEW.child_item_id

How to fix

We can use the same fix as before_delete_items_items.

Additional possible problems in triggers

Other problems might arrive from triggers. Here's a list with a rough explanation of the number of inserts that happens. I can be wrong analyzing the queries since I never touched those parts.

Into results_propagate:

• after_insert_groups_groups: one insert for each item_id for which the NEW.child_group_id has a result. There's also a check on permissions that might influence the worst case, I'm not sure but it might be "the group has at least a parent that can be viewed, and no children that can be viewed".
• after_update_groups_groups: same as after_insert_groups_groups
• after_insert_permissions_generated: one insert for each results of children items of the permission item_id, where participant_id is a child of the permission group_id
• after_update_permissions_generated: same as after_insert_permissions_generated

Into permissions_propagate:

• after_insert_items_items one insert for each entry in permissions_generated with item_id=NEW.parent_item_id
• after_update_items_items: same as after_insert_items_items