Anomie added a comment.
We could probably eliminate the join with `page` in this query, but other
than that I don't see much opportunity for improvement.
wikiadmin@10.64.32.113(wikidatawiki)> explain SELECT pl_namespace AS
`namespace`,pl_title AS `title`,COUNT(*) AS `value` FROM `pagelinks` GROUP BY
pl_namespace,pl_title HAVING COUNT(*) > 1 ORDER BY value DESC LIMIT 5000;
+------+-------------+-----------+-------+---------------+--------------+---------+------+------------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+------+-------------+-----------+-------+---------------+--------------+---------+------+------------+----------------------------------------------+
| 1 | SIMPLE | pagelinks | index | NULL | pl_namespace |
265 | NULL | 1311306562 | Using index; Using temporary; Using filesort |
+------+-------------+-----------+-------+---------------+--------------+---------+------+------------+----------------------------------------------+
The query is trying to find the 5000 (pl_namespace,pl_title) pairs with the
most rows in `pagelinks`, so unfortunately it's going to have to scan the whole
huge table and collect those counts. That expensive query //is// cached;
updateSpecialPages.php is the maintenance script that runs periodically to
update that cache.
It looks like it should be running on the vslow replica; if that's not the
case we could at least fix that.
TASK DETAIL
https://phabricator.wikimedia.org/T235265
EMAIL PREFERENCES
https://phabricator.wikimedia.org/settings/panel/emailpreferences/
To: Anomie
Cc: Aklapper, Ladsgroup, Anomie, Marostegui, darthmon_wmde, DannyS712, Nandana,
Lahi, Gq86, Lsherwinforone, GoranSMilovanovic, Jayprakash12345, QZanden,
LawExplorer, Sethakill, _jensen, rosalieper, Pchelolo, Wong128hk,
Wikidata-bugs, aude, Mbch331
_______________________________________________
Wikidata-bugs mailing list
Wikidata-bugs@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs
