Y25-044 - As a TL (Tom W) I would like to improve the performance of the sample_tracking_view by using cached data so that it is usable by stakeholders.

[TWJW-SANGER]

User story
As a TL (Tom W) I would like to improve the performance of the sample_tracking_view by using cached data so that it is usable by stakeholders.

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:

• A table named seqops_tracking_per_plate is created with columns identical to existing sample_tracking_view
• Indices are created manifest plate barcode, study name and study id columns.
• A MySQL Event is scheduled to re-populate the seqops_tracking_per_plate table once a day at 1:30 am with the same output as the current sample_tracking_view
• The existing sample_tracking_view is redefined to "SELECT * FROM seqops_tracking_per_plate" to maintain existing queries while benefiting from the performance boost.
• The table, event and modified view are present in each environment of in Training, UAT and Prod.
• The Confluence page "Sample Tracking. Report" updated with new names, performance metrics and implementation details.

Dependencies

This story is blocked by the following dependencies:

• Y25-043 - As a Reporting Analyst (Osama M) I would like a new table in mlwh_reporting for tracking at a sample level so that we can monitor pipelines that cherrypick from source plates over time. #38

Additional context
The current sample_tracking_view takes at least 10 mins to query and has a heavy load on the server.

As part of this story we would like to try using the MySQL Event Scheduler instead of making a view. This is essentially creating 'materialised' view in MySQL.

I have checked this with the DBA's (FS ticket SR-8179) who think it is okay as long as:

Avoid DELETE and prefer TRUNCATE TABLE (See confluence page on MLWH - Guidelines for bulk updates, migrations and performance.)
Be aware of is that all MySQL events have an owner, or more accurately, a definer. When a database is dumped using the mysqldump tool, and the --events option is included, then the dump file includes one or more CREATE EVENT commands to re-create the events in the dumped database. The name of the definer will be included in these commands. If the dump file is then imported into a different MySQL instance, such dumping a production database to import into a test/development instance, then the definer account must exist in the receiving instance, otherwise the CREATE EVENT command will fail, and the entire import will be abandoned, leaving you with a partially-imported database. We may need to work with you to ensure that the weekend cron jobs which re-populate your test/development databases from dumps of the production databases will not fall foul of this.

Consider what, if any, refactoring or re-use we could use with the seq_ops_tracking_per_sample table event SQL.