Running correlations on some traits, for example
takes such a huge amount of time, that the either the back-end server, or the browser times out.
As part of the optimisation effort, we need to look into materialised views to speed up fetching of the data.
MySQL/MariaDB does not support materialised views (there is no `CREATE MATERIALIZED VIEW ...` command or equivalent) but there are ways around that, to simulate the materialized views.
There is some work on
that should allow intermediate materialised views to be stored in lmdb
There might need to be multiple materialised views for the different types of datasets/traits, i.e.
The implementation of the materialised views will need to concern itself with the following issues:
Maybe, if we are going down the materialised views road, we should also have migrations in place - that way we can keep track of what schema, triggers, views, etc are in place, and be able to reproduce them automatically (or semi-automatically).
The reason for the concern above is, for example, The developers (alex, arun, bonz, fredm, etc) can implement whatever schema changes, triggers and views locally, but they do not (and probably should not) have access to production. As such, there should be provided a sort of CLI endpoint for the deployment system (or person) to run to implement the changes on the production (and CI/CD) database to get similar results.
The problem here, is that the migration might be moot, if the data is then moved out of the database, as is being planned.
Possible candidate queries for materialisation are:
The method above is doing way too much - it should probably be split into separate methods for each class, to simplify the code a little and make it clearer what each part does before reworking the queries for the materialized view.
The method above is also doing way too much.
Both methods above do not have the metadata, so probably also have a look at adding the metadata to the materialized views
In my early (2022-10-10) assessment, for each of the different types of datasets/traits mentioned in the description, we might need the data for the following "entities" in the materialized views:
The following are the database tables classified into their various functions (in the order order: Genotypes, mRNA, Phenotypes, Temp):
#### Dataset Information Tables
#### Dataset Metadata Tables
#### Sample Data
There might also need to be materialised views for the `Tissue*` tables, i.e.: