Once the local_user_id and global_user_id fields are completely populated on the Beta Cluster, we should add the following indexes:
These will not be UNIQUE indexes.
Make sure this is run against the master database:
sql --write centralauth
| Status | Subtype | Assigned | Task | ||
|---|---|---|---|---|---|
| Open | Feature | None | T66475 Make crosswiki bits and pieces truly global (tracking) | ||
| Open | Feature | None | T5525 Cross-wiki watchlists | ||
| Declined | None | T148239 Add indices for local_user_id and global_user_id in Beta | |||
| Resolved | • Niharika | T148241 Fully populate local_user_id and global_user_id fields in Beta | |||
| Resolved | • Niharika | T142503 Create a maintenance script for populating the local_user_id and global_user_id fields in the centralauth localuser table | |||
| Resolved | • Marostegui | T141951 Add local_user_id and global_user_id fields to localuser table in centralauth database |
I think the column pairs are correct. Placing lu_local_id and lu_global_id first in the composite indices will allow them to be used even if the lu_wiki specifier is omitted from a query for some reason. I think these indices should both be marked as UNIQUE as well.
This is my opinion only.
It's hard to know exactly what we'll need beforehand, but I think those should be a good start.
Then I would wait and not add (lu_local_id, lu_wiki). It is the PRIMARY KEY, in inverse order. If for some reason most of the queries require that order, we could switch the primary key order. However, based on your own statement- I would see searches being done on a local user AND wiki, but not without the wiki.
I think these indices should both be marked as UNIQUE as well.
aaron has been recomending against adding more unique indexes than the primary key to get advantage of the InnoDB change buffer. While I do not agree 100% with that, I wouldn't go against that recommendation without discussing it first.
My recommendation is, as of now, just add (lu_global_id, lu_wiki) (non unique) and wait for more code before adding unnecesary indexes (as of now). When we have a primary key, adding secondary keys is very very easy now.
The indexing is not clear yet, maybe later we may want to do some covering index to speed up look ups, so let's start simpler for now; get more complex later.
Once the local_user_id and global_user_id fields are completely populated on the Beta Cluster, we should add the following indexes:
That is the case now, according to resolved subtask T148241: Fully populate local_user_id and global_user_id fields in Beta?
Those indixes don't exist in the schema and I don't really see a reason to drift here.