worldcubeassociation.org icon indicating copy to clipboard operation
worldcubeassociation.org copied to clipboard

Optimize records and rankings queries

Open gregorbg opened this issue 5 months ago • 0 comments

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 explicit JOIN statements for readability
  • Pre-compute the gender value in the Concise*Results directly, to avoid at least one JOIN
  • Introduce a new ComputeRankingsRecords (CRR) job
    • Creates a table that "unfolds" value1value5 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
  • Add more indexes to the existing Concise*Results tables, mostly to accelerate GROUP BY statements
  • (Bonus: change the ID columns of the Concise*Results tables so that they match the Results and the Ranks* tables)

gregorbg avatar Sep 20 '24 12:09 gregorbg