[keyserver] Introduce index to optimize new fetchThreadInfos query
Summary:
In the following diff, I'll update fetchThreadInfos to use a query like this:
SELECT t.id, t.name, t.parent_thread_id, t.containing_thread_id, t.community, t.depth, t.color, t.description, t.type, t.creation_time, t.source_message, t.replies_count, t.avatar, t.pinned_count, m.user, m.role, m.permissions, m.subscription, m.last_read_message < m.last_message AS unread, m.sender, up.id AS upload_id, up.secret AS upload_secret FROM memberships mm LEFT JOIN threads t ON t.id = mm.thread LEFT JOIN memberships m ON m.thread = t.id AND m.role >= 0 LEFT JOIN uploads up ON up.container = t.id WHERE mm.user = 256 AND mm.role > -1 ORDER BY m.user ASC
The WHERE clause there filters memberships for a specific user and a range of roles. To optimize this query, I'm introducing an index on user > role > thread.
The inclusion of thread at the end may be useful for codepaths where we further filter by thread (eg. update-creator.js).
Test Plan: I ran the migration locally and confirmed that it worked
Reviewers: tomek, atul
Reviewed By: tomek
Differential Revision: https://phab.comm.dev/D8512