When doing cross tracker search we must ensure that tracker is not deleted but there was no index on that field. Reference query: ```sql SELECT SQL_CALC_FOUND_ROWS DISTINCT tracker_artifact.tracker_id, tracker_artifact.id, tracker_changeset_value_title.value AS title FROM tracker_artifact INNER JOIN tracker_changeset AS last_changeset ON (tracker_artifact.last_changeset_id = last_changeset.id) INNER JOIN tracker ON (tracker_artifact.tracker_id = tracker.id) INNER JOIN `groups` ON (`groups`.group_id = tracker.group_id) LEFT JOIN (tracker_changeset_value AS changeset_value_title INNER JOIN tracker_semantic_title ON (tracker_semantic_title.field_id = changeset_value_title.field_id) INNER JOIN tracker_changeset_value_text AS tracker_changeset_value_title ON (tracker_changeset_value_title.changeset_value_id = changeset_value_title.id)) ON (tracker_semantic_title.tracker_id = tracker_artifact.tracker_id AND changeset_value_title.changeset_id = tracker_artifact.last_changeset_id) LEFT JOIN (tracker_changeset_value AS changeset_value_status INNER JOIN (SELECT DISTINCT field_id FROM tracker_semantic_status AS field WHERE field.tracker_id IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)) AS equal_status_field ON (equal_status_field.field_id = changeset_value_status.field_id) INNER JOIN tracker_changeset_value_list AS tracker_changeset_value_status ON (tracker_changeset_value_status.changeset_value_id = changeset_value_status.id)) ON (changeset_value_status.changeset_id = tracker_artifact.last_changeset_id) WHERE `groups`.status = 'A' AND tracker.deletion_date IS NULL AND tracker_artifact.tracker_id IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) AND changeset_value_status.changeset_id IS NOT NULL AND tracker_changeset_value_status.bindvalue_id IN (SELECT open_value_id FROM tracker_semantic_status AS field WHERE field.tracker_id IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)) ORDER BY tracker_artifact.id DESC LIMIT ?, ? ``` Query time before index: 16s Query time after index: 0.79s Closes request #37239 cross tracker search missing index Change-Id: I350f837956d96f857a19c2f173dacfbba587c81d

+37 −1

Author

Manuel Vacelet (vaceletm) 2024-03-26 09:37

Committer

Manuel Vacelet (vaceletm) 2024-03-26 10:38

Hash bf63e75cf6abc09f21b456b2dae108cc356d7c4a

Parent 98111e936317066c56b8edaba986f01923181f94

Reference git #tuleap/stable/bf63e75cf6abc09f21b456b2dae108cc356d7c4a