Sorry. I missed this email cause gmail sent it to the "Promotions" folder. Let me think about it a bit.

So I looked into this. Adding the column is one aspect, you definitely need to add an index for it too. For the index, since you're not looking for similar results or ordering, maybe you can use hash indexes to improve lookup and reduce space.

The table is rather big (36GB) and it will grow faster given temporary accounts and the initiatives next year to increase logged in users. So I am worried about the scalability of this table.

Here is my suggestion: To offset the growth added by the new column, you can do a couple of things to improve things and even more:

1- There are two indexes on the table that don't make sense to me:

KEY `gu_locked` (`gu_name`,`gu_locked`)
KEY `gu_hidden_level` (`gu_name`,`gu_hidden_level`)

We do have a unique index on gu_name already so the extra columns won't provide any extra look up benefits. Only thing I can think of is that they are covering indexes but I don't know if it's actually the case and whether the performance gain (which would be quite small) is really worth the extra index.

2- Once you added the column and the index for email normalized, maybe drop the index on gu_email (KEY gu_email (gu_email)) but make sure any query that looks up based on gu_email, use the normalized version instead.

3- This is a bit of work but very worth it: gu_password_reset_key and gu_password_reset_expiration are basically empty for every row. (81M rows out of 83.6M). If you create a table similar to editcount one and only insert the row when there is actually a reset key, that could remove a lot of nulls and clean up a decent chunk of the table.

4- Is gu_cas_token really needed? Maybe it can be a smallint instead and then simply overflow back to 1 if it gets over the limit. That'll save 160MB which is not much but if it's easy to do, why not.

Note I propose to split several columns to dedicated tables. T391785: Introduce dedicated tables for CentralAuth passwords, emails and tokens

temporary accounts

Once passwords, emails and are in dedicated tables, temporary account will no longer have footprint on password and email table. They will also have no footprint on token table after 90 days once CentralAuth's resetAuthToken() is modified to remove the token entry (instead of setting it to a random string).

maybe drop the index on gu_email (KEY gu_email (gu_email))

This assumes the way to normalize email never changes. Otherwise it will be awkward.

gu_locked

See T373388: Merge CentralAuth locks into GlobalBlocking.

In T422656#11810264, @Bugreporter wrote:

Note I propose to split several columns to dedicated tables. T391785: Introduce dedicated tables for CentralAuth passwords, emails and tokens

That won't help much in terms of storage. because you need to repeat the user id, so it's actually gonna increase the size not decrease. Whether that can be compensated with not storing rows for temp accounts need be measured (not to mention that if you want to store emails for temp accounts and co, it might immediately nullify any gain).

In T422656#11810280, @Bugreporter wrote:

maybe drop the index on gu_email (KEY gu_email (gu_email))

This assumes the way to normalize email never changes. Otherwise it will be awkward.

I'm asking the index to be dropped, not the column.

make sure any query that looks up based on gu_email, use the normalized version instead.

It will be awkward if we change how "normalized version" is defined in the future

not to mention that if you want to store emails for temp accounts and co, it might immediately nullify any gain

What I mean: temporary accounts should not have rows in the proposed table at all, not even storing null or empty string.

That won't help much in terms of storage. because you need to repeat the user id, so it's actually gonna increase the size not decrease.

But after we split them to new tables, the next step would be moving password, email and token tables to a new cluster (T120484: Store CentralAuth password hashes outside the main database cluster).