MysqlUpdater::doWatchlistUpdate takes over two minutes to first check if there are any pages.
Then it seems to fire an update query anyway (To me it looks like as if MediaWiki is creating new broken entries, because on second run it completes quickly) that also takes minutes. This unnecessarily slows down database updates. As both the SELECT and INSERT queries seem to be doing a table scan, the SELECT seems redundant, only doubling the execution time.
SlowTimer [151948ms] at runtime/ext_mysql: slow query: SELECT /* MysqlUpdater::doWatchlistUpdate nike@web1.trans... */ count(*) FROM `bw_watchlist` WHERE wl_namespace & 1 LIMIT 1 Adding missing watchlist talk page rows... SlowTimer [211876ms] at runtime/ext_mysql: slow query: INSERT /* MysqlUpdater::doWatchlistUpdate nike@web1.trans... */ IGNORE INTO `bw_watchlist` (wl_user,wl_namespace,wl_title,wl_notificationtimestamp) SELECT wl_user,wl_namespace | 1,wl_title,wl_notificationtimestamp FROM `bw_watchlist` WHERE (NOT (wl_namespace & 1)) done. Adding missing watchlist subject page rows... SlowTimer [117847ms] at runtime/ext_mysql: slow query: INSERT /* MysqlUpdater::doWatchlistUpdate nike@web1.trans... */ IGNORE INTO `bw_watchlist` (wl_user,wl_namespace,wl_title,wl_notificationtimestamp) SELECT wl_user,wl_namespace & ~1,wl_title,wl_notificationtimestamp FROM `bw_watchlist` WHERE (wl_namespace & 1) done.
MariaDB [translatewiki_net]> explain SELECT /* MysqlUpdater::doWatchlistUpdate nike@web1.trans... */ count(*) FROM `bw_watchlist` WHERE wl_namespace & 1 LIMIT 1; +------+-------------+--------------+-------+---------------+-----------------+---------+------+---------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+--------------+-------+---------------+-----------------+---------+------+---------+--------------------------+ | 1 | SIMPLE | bw_watchlist | index | NULL | namespace_title | 261 | NULL | 4641661 | Using where; Using index | +------+-------------+--------------+-------+---------------+-----------------+---------+------+---------+--------------------------+
MariaDB [translatewiki_net]> explain INSERT /* MysqlUpdater::doWatchlistUpdate nike@web1.trans... */ IGNORE INTO `bw_watchlist` (wl_user,wl_namespace,wl_title,wl_notificationtimestamp) SELECT wl_user,wl_namespace | 1,wl_title,wl_notificationtimestamp FROM `bw_watchlist` WHERE (NOT (wl_namespace & 1)) ; +------+-------------+--------------+------+---------------+------+---------+------+---------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+--------------+------+---------------+------+---------+------+---------+------------------------------+ | 1 | SIMPLE | bw_watchlist | ALL | NULL | NULL | NULL | NULL | 4641663 | Using where; Using temporary | +------+-------------+--------------+------+---------------+------+---------+------+---------+------------------------------+