worldcubeassociation.org
worldcubeassociation.org copied to clipboard
Optimize records and rankings queries
We cannot pre-compute the whole tables, the number of possible combinations of parameters is simply too big for that.
However we can work with more efficient indexes, and build a few auxiliary tables that unfold Results
once instead of unfolding them upon every request.
Together with the Redis caches, this should be enough to re-enable some of the filters.
Here's a more complete list of what this PR is doing
- Replacing implicit cross joins in the
WHERE
section by explicitJOIN
statements for readability - Pre-compute the
gender
value in theConcise*Results
directly, to avoid at least one JOIN - Introduce a new
ComputeRankingsRecords
(CRR) job- Creates a table that "unfolds"
value1
…value5
into separate rows, referencing their respective "parent" result via foreign key - Creates a table that pre-filters all result rows marked as
regionalRecord
in any capacity
- Creates a table that "unfolds"
- Add more indexes to the existing
Concise*Results
tables, mostly to accelerateGROUP BY
statements - (Bonus: change the ID columns of the
Concise*Results
tables so that they match theResults
and theRanks*
tables)