CirrusSearch is using a new query for counting incoming links (previously this was queried from ElasticSearch), it looks roughly like:
SELECT Count(1) FROM `pagelinks` WHERE ( pl_namespace = '0' AND pl_title = 'Meter') OR ( pl_namespace = '0' AND pl_title = 'Centimeter') OR ( pl_namespace = '0' AND pl_title = 'Kilometer') OR ( pl_namespace = '0' AND pl_title = 'Zentimeter') OR ( pl_namespace = '0' AND pl_title = 'Nanometer') OR ( pl_namespace = '0' AND pl_title = 'Millimeter') OR ( pl_namespace = '0' AND pl_title = 'Picometer') OR ( pl_namespace = '0' AND pl_title = 'Femtometer') OR ( pl_namespace = '0' AND pl_title = 'Dezimeter') OR ( pl_namespace = '0' AND pl_title = 'Hektometer') OR ( pl_namespace = '0' AND pl_title = 'Dekameter') OR ( pl_namespace = '0' AND pl_title = 'Μm') OR ( pl_namespace = '0' AND pl_title = 'Myriam eter') OR ( pl_namespace = '0' AND pl_title = 'Pikometer') OR ( pl_namespace = '0' AND pl_title = 'Neuzoll') OR ( pl_namespace = '0' AND pl_title = 'Mikrometer_(Einheit)') LIMIT 1;
the available indexes look like:
UNIQUE KEY `pl_from` (`pl_from`,`pl_namespace`,`pl_title`), KEY `pl_namespace` (`pl_namespace`,`pl_title`,`pl_from`), KEY `pl_backlinks_namespace` (`pl_namespace`,`pl_title`,`pl_from_namespace`,`pl_from`)
This generates the query plan:
+------+-------------+-----------+------+-------------------------------------+------------------------+---------+-------+-------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-----------+------+-------------------------------------+------------------------+---------+-------+-------+--------------------------+ | 1 | SIMPLE | pagelinks | ref | pl_namespace,pl_backlinks_namespace | pl_backlinks_namespace | 4 | const | 20025 | Using where; Using index | +------+-------------+-----------+------+-------------------------------------+------------------------+---------+-------+-------+--------------------------+
We can see from the key_len and the ref column this is only using the first part of the pl_backlinks_namespace index, the namespace, and then iterating to find the titles. Due to the cardinality here this is a very poor query plan and times out.
Limiting to a single title mysql chooses a better query plan:
mysql:wikiadmin@db1082 [dewiki]> explain SELECT count(1) FROM `pagelinks` WHERE (pl_namespace = '0' AND pl_title = 'Meter') LIMIT 1; +------+-------------+-----------+------+-------------------------------------+--------------+---------+-------------+-------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-----------+------+-------------------------------------+--------------+---------+-------------+-------+--------------------------+ | 1 | SIMPLE | pagelinks | ref | pl_namespace,pl_backlinks_namespace | pl_namespace | 261 | const,const | 10746 | Using where; Using index | +------+-------------+-----------+------+-------------------------------------+--------------+---------+-------------+-------+--------------------------+
Alternatively adding a USE INDEX (pl_namespace) also generates a better query plan
mysql:wikiadmin@db1082 [dewiki]> explain SELECT count(1) FROM `pagelinks` USE INDEX (`pl_namespace`) WHERE (pl_namespace = '0' AND pl_title = 'Meter') OR (pl_namespace = '0' AND pl_title = 'Centimeter') OR (pl_namespace = '0' AND pl_title = 'Kilometer') OR (pl_namespace = '0' AND pl_title = 'Zentimeter') OR (pl_namespace = '0' AND pl_title = 'Nanometer') OR (pl_namespace = '0' AND pl_title = 'Millimeter') OR (pl_namespace = '0' AND pl_title = 'Picometer') OR (pl_namespace = '0' AND pl_title = 'Femtometer') OR (pl_namespace = '0' AND pl_title = 'Dezimeter') OR (pl_namespace = '0' AND pl_title = 'Hektometer') OR (pl_namespace = '0' AND pl_title = 'Dekameter') OR (pl_namespace = '0' AND pl_title = 'Μm') OR (pl_namespace = '0' AND pl_title = 'Myriameter') OR (pl_namespace = '0' AND pl_title = 'Pikometer') OR (pl_namespace = '0' AND pl_title = 'Neuzoll') OR (pl_namespace = '0' AND pl_title = 'Mikrometer_(Einheit)') LIMIT 1; +------+-------------+-----------+-------+---------------+--------------+---------+------+-------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-----------+-------+---------------+--------------+---------+------+-------+--------------------------+ | 1 | SIMPLE | pagelinks | range | pl_namespace | pl_namespace | 261 | NULL | 13902 | Using where; Using index | +------+-------------+-----------+-------+---------------+--------------+---------+------+-------+--------------------------+
This query runs in ~10ms when using an appropriate query plan:
+----------+ | count(1) | +----------+ | 8536 | +----------+ 1 row in set (0.01 sec)