Page MenuHomePhabricator

Add indices for local_user_id and global_user_id in Beta
Open, Needs TriagePublic

Description

Once the local_user_id and global_user_id fields are completely populated on the Beta Cluster, we should add the following indexes:

  • (lu_local_id, lu_wiki)
  • (lu_global_id, lu_wiki)

These will not be UNIQUE indexes.

Make sure this is run against the master database:
sql --write centralauth

Event Timeline

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptOct 14 2016, 8:16 PM

Doesn't the update script take care of this?

Ugh, right, CentralAuth. Never mind.

kaldari updated the task description. (Show Details)Oct 18 2016, 10:06 PM
kaldari updated the task description. (Show Details)Oct 19 2016, 5:34 AM

@bd808, @jcrespo: Do the indexes I suggested in the description sound reasonable? It's hard to know exactly what we'll need beforehand, but I think those should be a good start.

bd808 added a comment.Oct 19 2016, 3:36 PM

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.

jcrespo added a comment.EditedOct 20 2016, 7:50 AM

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.

kaldari updated the task description. (Show Details)Oct 20 2016, 5:20 PM
kaldari moved this task from To be estimated/discussed to Bug backlog on the Community-Tech board.