The CheckUser extension currently stores both the IP address and the hexadecimal representation of the IP address in separate columns. Based on https://codesearch.wmcloud.org/search/?q=cuc_ip&files=&excludeFiles=&repos=, it seems that there are no situations where the cuc_ip_hex column could not be used instead after passing it through IPUtils::formatHex. Removing the ip_hex column cannot be done as it used to filter for results by IP addresses. That means removing the ip column is the better option to remove this duplication.

To do this, the actor_ip_time indexes will need to be updated to use the ip_hex column instead of the ip column. This should be okay, but may produce an increase in the size of the index depending on how much a column being NULL could affect the index size. However, I would expect that this would lead to a overall decrease in the size for the result tables.

Example

For example, the cu_changes currently looks like:

  ...
  cuc_ip VARCHAR(255) DEFAULT '',
  cuc_ip_hex VARCHAR(255) DEFAULT NULL,
  ...
  INDEX cuc_ip_hex_time (cuc_ip_hex, cuc_timestamp),
  INDEX cuc_xff_hex_time (cuc_xff_hex, cuc_timestamp),
  INDEX cuc_timestamp (cuc_timestamp),
  INDEX cuc_actor_ip_time (cuc_actor, cuc_ip, cuc_timestamp),
  PRIMARY KEY(cuc_id)
) /*$wgDBTableOptions*/;

This ticket would propose that it be changed to the following:

  ...
  cuc_ip_hex VARCHAR(255) DEFAULT NULL,
  ...
  INDEX cuc_ip_hex_time (cuc_ip_hex, cuc_timestamp),
  INDEX cuc_xff_hex_time (cuc_xff_hex, cuc_timestamp),
  INDEX cuc_timestamp (cuc_timestamp),
  INDEX cuc_actor_ip_hex_time (cuc_actor, cuc_ip_hex, cuc_timestamp),
  PRIMARY KEY(cuc_id)
) /*$wgDBTableOptions*/;

This change would also be applied to the cu_log_event and cu_private_event tables.