Keep track of projects a user contributed to
Description
Finding projects that are visible to a particular user is quite expensive. In https://gitlab.com/gitlab-org/gitlab-ce/issues/40525#note_48635425, @yorickpeterse proposes to create a separate table that keeps track of projects a user contributed to. This can be used to considerably speed up queries that involve project visibility.
For related issues that would benefit from this see
- https://gitlab.com/gitlab-org/gitlab-ce/issues/40525
- https://gitlab.com/gitlab-org/gitlab-ce/issues/42877
We're going to tackle this in two steps/releases:
- Create and populate a table
user_contributed_projects
to track projects a user contributed to. The goal is to get this in 10.6 - Leverage the above table to speed up queries that involve project visibility. This will be done in 10.7 once the table has been populated and is kept in sync. Issue to be created.
Proposal
In this issue, we only tackle part 1:
- Create table
user_contributed_projects
- Populate table in a migration
- Keep track of contributions and maintain consistency of table
Schema
The schema of this table would be very simple and only contain two columns:
- The user ID
- The ID of the project
Both have a foreign key to the appropriate table with a cascading delete
Usage
Using this table would be very simple: everywhere we currently do WHERE projects.visibility_level IN (...)
we INNER JOIN this new table onto projects. Using this approach the visibility_level filter should only apply to projects you actually contributed to. We may need to make further changes, but I think this should be enough.