Page MenuHomePhabricator

Remove redundant global_user_groups.gug_user and global_group_permissions.ggp_group indexes
Closed, ResolvedPublic

Description

-- Global user groups.
CREATE TABLE /*_*/global_user_groups (
  gug_user int(11) not null,
  gug_group varchar(255) not null,

  PRIMARY KEY (gug_user,gug_group)
) /*$wgDBTableOptions*/;

CREATE INDEX /*i*/gug_user ON /*_*/global_user_groups (gug_user);
CREATE INDEX /*i*/gug_group ON /*_*/global_user_groups (gug_group);

The primary key indexes gug_user as part of the combined primary index, so a duplicate index for gug_user is not required

MariaDB [centralauth]> show indexes from global_user_groups;
+--------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table              | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| global_user_groups |          0 | PRIMARY   |            1 | gug_user    | A         |        1678 |     NULL | NULL   |      | BTREE      |         |               |
| global_user_groups |          0 | PRIMARY   |            2 | gug_group   | A         |        1678 |     NULL | NULL   |      | BTREE      |         |               |
| global_user_groups |          1 | gug_user  |            1 | gug_user    | A         |        1678 |     NULL | NULL   |      | BTREE      |         |               |
| global_user_groups |          1 | gug_group |            1 | gug_group   | A         |          44 |     NULL | NULL   |      | BTREE      |         |               |
+--------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

Same for ggp_group

-- Global group permissions.
CREATE TABLE /*_*/global_group_permissions (
  ggp_group varchar(255) not null,
  ggp_permission varchar(255) not null,

  PRIMARY KEY (ggp_group, ggp_permission)
) /*$wgDBTableOptions*/;

CREATE INDEX /*i*/ggp_group ON /*_*/global_group_permissions (ggp_group);
CREATE INDEX /*i*/ggp_permission ON /*_*/global_group_permissions (ggp_permission);

and

MariaDB [centralauth]> show indexes from global_group_permissions;
+--------------------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table                    | Non_unique | Key_name       | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| global_group_permissions |          0 | PRIMARY        |            1 | ggp_group      | A         |          42 |     NULL | NULL   |      | BTREE      |         |               |
| global_group_permissions |          0 | PRIMARY        |            2 | ggp_permission | A         |         427 |     NULL | NULL   |      | BTREE      |         |               |
| global_group_permissions |          1 | ggp_group      |            1 | ggp_group      | A         |          42 |     NULL | NULL   |      | BTREE      |         |               |
| global_group_permissions |          1 | ggp_permission |            1 | ggp_permission | A         |         427 |     NULL | NULL   |      | BTREE      |         |               |
+--------------------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

Event Timeline

Reedy renamed this task from Remove redundant global_user_groups.gug_user index to Remove redundant global_user_groups.gug_user and global_group_permissions.ggp_group indexes.Feb 22 2020, 4:57 PM
Reedy updated the task description. (Show Details)

Change 574188 had a related patch set uploaded (by Reedy; owner: Reedy):
[mediawiki/extensions/CentralAuth@master] Remove redundant indexes from global_user_groups and global_group_permissions

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

Change 574188 merged by jenkins-bot:
[mediawiki/extensions/CentralAuth@master] Remove redundant indexes from global_user_groups and global_group_permissions

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

Reedy claimed this task.