unc-csxl / csxl.unc.edu

CS Experience Labs' web application.
https://csxl.unc.edu
MIT License
10 stars 11 forks source link

Index Academics join table columns that back common queries #497

Closed KrisJordan closed 3 months ago

KrisJordan commented 3 months ago

By adding Indexes to Academics' SectionMemberEntity (by user and by section), and SectionEntity (by term and by course), we should see an automatic performance increase for some common queries (e.g. look up all members of a section or all sections for a user).

This PR also drops the application_id column from SectionMemberEntity as a cleanup while we're running a migration on the table. When the time comes, this information (linking a section member to their TA application) can be held in a different entity, for example a TAAssignmentEntity which can join an application to a member (with additional information such as hiring level, and so on).

Migration created and validated per the backend/migrations/README validation steps.

KrisJordan commented 3 months ago

Yes, precisely: adding secondary indexes leads to the rdbms maintaining a separate B-tree (or comparable) per index. Primary keys will automatically be indexed.

When a secondary index has multiple columns, you get the lookup benefits when looking up by either the first column in the index or both columns.

Without any indexes to rely on, searches will linearly scan results.

The trade-off is slower writes and some space, but for our join tables and typical cases, going from o(n) reads to o(log n) is worth the win (esp for joins where you start trending toward o(n^2) without an index to rely on).