Change 773913 had a related patch set uploaded (by Majavah; author: Majavah):

[mediawiki/extensions/CentralAuth@master] GlobalUsersPager: add gu_id to GROUP BY

https://gerrit.wikimedia.org/r/773913

Change 773913 merged by jenkins-bot:

[mediawiki/extensions/CentralAuth@master] GlobalUsersPager: add gu_id to GROUP BY

https://gerrit.wikimedia.org/r/773913

Let me know if you want help in the query when re-applying it.

Maybe the query could be rewritten to support also postgres. The use of two buildGroupConcatField with same alias gives errors on postgres. IFNULL is also a problem. Due to gug_expiry a timestamp on postgres it is not possible to group it with the "null", because only values of the same type could be group concat. See https://gerrit.wikimedia.org/r/c/mediawiki/extensions/CentralAuth/+/766221/2/includes/Special/GlobalUsersPager.php for an experiment.

The same pager for non-global users in core is using two queries instead of group concats.

I tried some alternative EXPLAINs on the stats machines yesterday, but couldn’t get MariaDB back into the “good” query plan. Maybe we need an index on gu_name, gu_id to be able to group by both columns efficiently? (Even though the existing gu_name index should already end with the gu_id internally, since that’s the primary key…)

I think two separate queries, like in core, might be better.

I also just realized that the all-in-one query does two separate group concats for the groups and expiries, and then combines them again:

$groups = explode( '|', $row->gug_group );
$expiries = explode( '|', $row->gug_expiry );
$combined = array_combine( $groups, $expiries );

But this assumes that both group concats will concatenate their elements in the same order, which I don’t think is guaranteed (“the resulting delimiter-separated values have no defined sort order”, IDatabase::buildGroupConcatField()). I think that’s another argument for splitting this part out into a separate query.

Change 776182 had a related patch set uploaded (by Majavah; author: Majavah):

[mediawiki/extensions/CentralAuth@master] GlobalUsersPager: Use a separate groups query

https://gerrit.wikimedia.org/r/776182

Change 776182 merged by jenkins-bot:

[mediawiki/extensions/CentralAuth@master] GlobalUsersPager: Use a separate groups query

https://gerrit.wikimedia.org/r/776182