Y25-122 - As a TL (Tom) I would like to improve the performance to the plate level tracking query so that we can add additional features and reduce load

[TWJW-SANGER]

User Story
As a TL (Tom) I would like to improve the performance to the plate level tracking query so that we can add additional features and reduce load

Who are the primary contacts for this story
Tom W
Laura H

Who is the nominated tester for UAT
Testing can be performed by the development team - see acceptance criteria.

Acceptance criteria
To be considered successful the solution must allow:

• The run time of the plate level tracking query that populates the seqops_tracking_per_plate table is reduced.
• The Confluence page "Sample Tracking Report" is reviewed and updated if required.
• The Confluence page "MLWH_Events 101 And Useful Queries" is updated if required.
• An email detailing any changes is sent to the "MultiLIMS Warehouse Stakeholders" as detailed on the Confluence page "Process For Deploying Updates To Applications Requiring Downtime"

Additional Context
To improve the query performance we can consider any of:

• Adding indexes to tables.
• Making the uuid formats used to link events and samples match (see below).
• Refactoring the query to share any common CTE subqueries with the sample level tracking as materialised tables.

Note one option to make the uuid columns match is to introduce an extra column to either the sample or subject table to contain the equivalent uuid but computed from the other. MySQL has a interested feature for doing this that stores the value rather than recalculting it all the time, see: https://dev.mysql.com/doc/refman/8.4/en/create-table-generated-columns.html
Probably worth checking with the DBA team on this approach though.

I would like to aim for one format of uuids to be our prefered format to store in our databases.