Wikimedia Bugzilla is closed!

The API runs the following query:

SELECT /* ApiQueryCategoryMembers::run */  cl_from,cl_sortkey,cl_type,page_namespace,page_title,cl_timestamp  FROM page,categorylinks  FORCE INDEX (cl_timestamp) WHERE cl_to = 'Copy_to_Wikimedia_Commons_(bot-assessed)' AND (cl_from=page_id)  ORDER BY cl_timestamp,cl_from LIMIT 501;

CREATE TABLE categorylinks (
  cl_from int(8) unsigned NOT NULL DEFAULT '0',
  cl_to varbinary(255) NOT NULL DEFAULT '',
  cl_sortkey varbinary(230) NOT NULL DEFAULT '',
  cl_timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  cl_sortkey_prefix varbinary(255) NOT NULL DEFAULT '',
  cl_collation varbinary(32) NOT NULL DEFAULT '',
  cl_type enum('page','subcat','file') NOT NULL DEFAULT 'page',
  UNIQUE KEY cl_from (cl_from,cl_to),
  KEY cl_timestamp (cl_to,cl_timestamp),
  KEY cl_collation (cl_collation),
  KEY cl_sortkey (cl_to,cl_type,cl_sortkey,cl_from)
) ENGINE=InnoDB DEFAULT CHARSET=binary

The forced cl_timestamp index is expected to utilize InnoDB extended indexes (where secondary indexes also store the clustered primary key fields) to avoid a filesort. Enwiki slaves up to MariaDB 5.5.34 do this correctly.

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: categorylinks
         type: ref
possible_keys: cl_timestamp
          key: cl_timestamp
      key_len: 257
          ref: const
         rows: 552330
        Extra: Using index condition; Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: page
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: enwiki.categorylinks.cl_from
         rows: 1
        Extra: 
2 rows in set (0.38 sec)

Slaves running 5.5.36+ revert to using a filesort despite apparently having identical categorylinks tables. Handler% stats suport the observation.

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: categorylinks
         type: ref
possible_keys: cl_from,cl_timestamp,cl_sortkey
          key: cl_sortkey
      key_len: 257
          ref: const
         rows: 466736
        Extra: Using index condition; Using where; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: page
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: enwiki.categorylinks.cl_from
         rows: 1
        Extra: 
2 rows in set (0.32 sec)

Important to note that the first enwiki 5.5.36 slave, from which others were cloned, did have data dumped and reloaded rather than being upgraded in place, so categorylinks was definitely recreated. Possibly something has changed with how the first UNIQUE index can be chosen as primary key, and the 6-byte rowid is in use instead?

Simply rebuilding the table has no effect:

ALTER TABLE categorylinks ENGINE=InnoDB;

But making the primary key explicit restores the expected behavior:

ALTER TABLE categorylinks DROP INDEX cl_from, ADD PRIMARY KEY (cl_from, cl_to);

Need to investigate what caused the change in behavior, and whether this affects any other mediawiki tables.