NOTE: Please feel free to change tags, I am not sure who/which Team tag in here.
We have seen that the php /srv/mediawiki-staging/multiversion/MWScript.php updateSpecialPages.php wikidatawiki --override --only=Mostlinked script that runs on mwmaint1002 is making very slow queries to run on wikidata (T234948#5562987).
The query itself is:
SELECT /* SpecialMostLinked::reallyDoQuery www-data@mwmain... */ pl_namespace AS `namespace`,pl_title AS `title`,COUNT(*) AS `value`,page_namespace FROM `pagelinks` LEFT JOIN `page` ON ((page_namespace = pl_namespace) AND (page_title = pl_title)) GROUP BY pl_namespace,pl_title,page_namespace HAVING COUNT(*) > 1 ORDER BY value DESC LIMIT 5000;
The query plan shows that the query needs some optimization or split:
root@db1087.eqiad.wmnet[wikidatawiki]> explain SELECT /* SpecialMostLinked::reallyDoQuery www-data@mwmain... */ pl_namespace AS `namespace`,pl_title AS `title`,COUNT(*) AS `value`,page_namespace FROM `pagelinks` LEFT JOIN `page` ON ((page_namespace = pl_namespace) AND (page_title = pl_title)) GROUP BY pl_namespace,pl_title,page_namespace 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 | PRIMARY | 265 | NULL | 1311268897 | Using index; Using temporary; Using filesort | | 1 | SIMPLE | page | eq_ref | name_title | name_title | 261 | wikidatawiki.pagelinks.pl_namespace,wikidatawiki.pagelinks.pl_title | 1 | Using index | +------+-------------+-----------+--------+---------------+------------+---------+---------------------------------------------------------------------+------------+----------------------------------------------+ 2 rows in set (0.00 sec)