SpecialUnconnectedPages is currently at the top of the slow queries dashboard – it’s not the slowest query (ca. 10 seconds on average), but its comparatively high volume pushes it to the #1 spot by sum time (ca. 900 seconds per hour). While this is currently an acute problem on dewiki, in principal all properties are vulnerable to this issue. We should fix it.
The query is the one that the special page generates when filtering by a namespace, specifically the main namespace (other namespaces are faster):
```lang=mysql
SELECT /* Wikibase\Client\Specials\SpecialUnconnectedPages::reallyDoQuery */ page_id AS `value`,page_namespace AS `namespace`,page_title AS `title`,0 AS `page_num_iwlinks` FROM `page` LEFT JOIN `page_props` ON ((page_id = pp_page) AND pp_propname IN ('wikibase_item','expectedUnconnectedPage') ) WHERE (page_namespace = 0) AND page_is_redirect = 0 AND (pp_propname IS NULL) ORDER BY value DESC LIMIT 101
```
----
Solution summary:
We introduced a new page prop, `unexpectedUnconnectedPage`, with a maintenance script to populate it. The page prop is set for pages in a Wikibase-enabled namespace that are neither connected to a Wikibase item nor have the `__EXPECTED_UNCONNECTED_PAGE__` magic word set. Its value is the negative namespace number; thus, sorting by descending `pp_sortkey` and `pp_page` produces first the newest unexpected unconnected pages in namespace 0 (“newest” meaning the ones with the highest page ID), then older pages in that namespace, then newest pages in the next namespace (e.g. 2), and so on. The new page prop makes it easy to efficiently find all unexpected unconnected pages, either within a certain namespace or across all Wikibase-enabled namespaces.
```lang=mysql
MariaDB [commonswiki]> SELECT NOW() AS asof\G SELECT -pp_sortkey AS ns, COUNT(*) AS count FROM page_props WHERE pp_propname = 'unexpectedUnconnectedPage' GROUP BY pp_sortkey ORDER BY COUNT(*) DESC;
*************************** 1. row ***************************
asof: 2022-10-10 15:39:15
1 row in set (0.001 sec)
+------+---------+
| ns | count |
+------+---------+
| 14 | 7162706 |
| 4 | 1134432 |
| 10 | 252933 |
| 486 | 60843 |
| 100 | 54691 |
| 0 | 35032 |
| 102 | 17000 |
| 106 | 6443 |
| 828 | 1021 |
| 460 | 768 |
| 12 | 768 |
| 490 | 160 |
| 104 | 24 |
+------+---------+
16 rows in set (2.648 sec)
```
(The implementation and migration was complicated by the fact that we initially made the page prop value the //positive// namespace number, and then had to follow up with additional Gerrit changes and a second run of maintenance script runs to turn it negative for better sorting.)
----
Wikibase changes:
- [SpecialUnconnectedPages: Remove cruft, add basic integration test](https://gerrit.wikimedia.org/r/c/mediawiki/extensions/Wikibase/+/763208)
- [Client: Add "unexpectedUnconnectedPage" page prop](https://gerrit.wikimedia.org/r/c/mediawiki/extensions/Wikibase/+/759969)
- [Add UnexpectedUnconnectedPagePrimer](https://gerrit.wikimedia.org/r/c/mediawiki/extensions/Wikibase/+/760014)
- [UnexpectedUnconnectedPagePrimer: Discard populated rows when selecting](https://gerrit.wikimedia.org/r/c/mediawiki/extensions/Wikibase/+/770091)
- [Add populateUnexpectedUnconnectedPagePageProp maintenance script](https://gerrit.wikimedia.org/r/c/mediawiki/extensions/Wikibase/+/770092)
- [SpecialUnconnectedPages: Order by pp_sortkey, page_id](https://gerrit.wikimedia.org/r/c/mediawiki/extensions/Wikibase/+/772399)
- [update.php: Add the "unexpectedUnconnectedPage" page prop](https://gerrit.wikimedia.org/r/c/mediawiki/extensions/Wikibase/+/770613)
- [Add missing ORDER BY to UnexpectedUnconnectedPagePrimer](https://gerrit.wikimedia.org/r/c/mediawiki/extensions/Wikibase/+/775819)
- [Sort Special:UnconnectedPages ascending in MIGRATION_NEW](https://gerrit.wikimedia.org/r/c/mediawiki/extensions/Wikibase/+/778516)
- [Update maintenance script file name and namespace](https://gerrit.wikimedia.org/r/c/mediawiki/extensions/Wikibase/+/779452)
- [Don’t use session-consistent connections in UnexpectedUnconnectedPagePrimer](https://gerrit.wikimedia.org/r/c/mediawiki/extensions/Wikibase/+/779455)
- [wmf.6](https://gerrit.wikimedia.org/r/c/mediawiki/extensions/Wikibase/+/779109) – [SAL](https://sal.toolforge.org/log/jfrxHYABa_6PSCT92loy)
- [wmf.7](https://gerrit.wikimedia.org/r/c/mediawiki/extensions/Wikibase/+/779110) – no SAL because merged before initial wmf.7 rollout
- [Check content, not title, for redirect status](https://gerrit.wikimedia.org/r/c/mediawiki/extensions/Wikibase/+/780869)
- [Use inverted values for the unexpectedUnconnectedPage page prop](https://gerrit.wikimedia.org/r/c/mediawiki/extensions/Wikibase/+/834548)
- [Client: Bump schema update key for unconnected pages](https://gerrit.wikimedia.org/r/c/mediawiki/extensions/Wikibase/+/835679)
- [UnexpectedUnconnectedPagePrimer: Don't rely on affectedRows](https://gerrit.wikimedia.org/r/c/mediawiki/extensions/Wikibase/+/836919)
- [Default unexpectedUnconnectedPage migration to MIGRATION_WRITE_BOTH](https://gerrit.wikimedia.org/r/c/mediawiki/extensions/Wikibase/+/789641)
- [Default unexpectedUnconnectedPage migration to MIGRATION_NEW](https://gerrit.wikimedia.org/r/c/mediawiki/extensions/Wikibase/+/789823)
- [Always use new "unexpectedUnconnectedPage" page prop format](https://gerrit.wikimedia.org/r/c/mediawiki/extensions/Wikibase/+/839547)
- [Write "unexpectedUnconnectedPage" page prop unconditionally](https://gerrit.wikimedia.org/r/c/mediawiki/extensions/Wikibase/+/789851)
- [Use "unexpectedUnconnectedPage" page prop unconditionally](https://gerrit.wikimedia.org/r/c/mediawiki/extensions/Wikibase/+/789852)
Config changes:
- [Write "unexpectedUnconnectedPage" page prop on Beta](https://gerrit.wikimedia.org/r/c/operations/mediawiki-config/+/768089)
- [SAL IS.php](https://sal.toolforge.org/log/c2n9l38Ba_6PSCT9qC54)
- [SAL IS-labs.php](https://sal.toolforge.org/log/Tmn-l38Ba_6PSCT9mS-h)
- [SAL Wikibase.php](https://sal.toolforge.org/log/_2n_l38Ba_6PSCT9qzBV)
- [Write "unexpectedUnconnectedPage" page prop on Test Wikidata clients](https://gerrit.wikimedia.org/r/c/operations/mediawiki-config/+/768090)
- [SAL](https://sal.toolforge.org/log/B_oBt38B8Fs0LHO5SLVC)
- [Write "unexpectedUnconnectedPage" page prop everywhere](https://gerrit.wikimedia.org/r/c/operations/mediawiki-config/+/773239)
- [SAL](https://sal.toolforge.org/log/WFej0H8B6FQ6iqKiTfOa)
- [Use "unexpectedUnconnectedPage" page prop on Beta](https://gerrit.wikimedia.org/r/c/operations/mediawiki-config/+/774847)
- [SAL](https://sal.toolforge.org/log/tIAn9X8B6FQ6iqKimSkQ)
- [Use "unexpectedUnconnectedPage" page prop on wikidataclient-test](https://gerrit.wikimedia.org/r/c/operations/mediawiki-config/+/779861)
- [SAL](https://sal.toolforge.org/log/eK5KI4AB6FQ6iqKiVO9F)
- [Read from the "unexpectedUnconnectedPage" page prop](https://gerrit.wikimedia.org/r/c/operations/mediawiki-config/+/780753)
- (no SAL, reverted after problems were discovered on mwdebug; fixed by “Check content, not title” above)
- [Revert "Read from the "unexpectedUnconnectedPage" page prop"](https://gerrit.wikimedia.org/r/c/operations/mediawiki-config/+/780630)
- [SAL](https://sal.toolforge.org/log/2bM5KIAB6FQ6iqKiwcL4)
- [Use "unexpectedUnconnectedPage" page prop everywhere](https://gerrit.wikimedia.org/r/c/operations/mediawiki-config/+/788356)
- [SAL](https://sal.toolforge.org/log/SR0UioAB6FQ6iqKiUOpI)
- [Wikibase: Set UnconnectedPage page prop format for test wikis](https://gerrit.wikimedia.org/r/c/operations/mediawiki-config/+/836803)
- [SAL](https://sal.toolforge.org/log/iQh6iYMB8Fs0LHO5ykW9)
- [Disable UnconnectedPagePagePropMigrationLegacyFormat for arwiki](https://gerrit.wikimedia.org/r/c/operations/mediawiki-config/+/838732)
- [SAL](https://sal.toolforge.org/log/EZZop4MBa_6PSCT9nE2m)
- [Disable UnconnectedPagePagePropMigrationLegacyFormat for ruwikinews](https://gerrit.wikimedia.org/r/c/operations/mediawiki-config/+/838746)
- [SAL](https://sal.toolforge.org/log/hpZ8p4MBa_6PSCT9G1ZC)
- [Disable UnconnectedPagePagePropMigrationLegacyFormat for commonswiki](https://gerrit.wikimedia.org/r/c/operations/mediawiki-config/+/838764)
- [SAL](https://sal.toolforge.org/log/31u_p4MB6FQ6iqKiVQEo)
- [Disable UnconnectedPagePagePropMigrationLegacyFormat for enwiki/zhwiki](https://gerrit.wikimedia.org/r/c/operations/mediawiki-config/+/838788)
- [SAL](https://sal.toolforge.org/log/fCQeqIMB8Fs0LHO5uoXi)
- [Disable UnconnectedPagePagePropMigrationLegacyFormat for enwiktionary/frwiki](https://gerrit.wikimedia.org/r/c/operations/mediawiki-config/+/838801)
- [SAL](https://sal.toolforge.org/log/71tOqIMB6FQ6iqKiw60J)
- [Disable UnconnectedPagePagePropMigrationLegacyFormat for three wikis](https://gerrit.wikimedia.org/r/c/operations/mediawiki-config/+/839440)
- [SAL](https://sal.toolforge.org/log/bpuArIMBa_6PSCT9H2pJ)
- [Disable UnconnectedPagePagePropMigrationLegacyFormat for nine wikis](https://gerrit.wikimedia.org/r/c/operations/mediawiki-config/+/839454)
- [SAL](https://sal.toolforge.org/log/KiiWrIMB8Fs0LHO5w8Mf)
- [Disable UnconnectedPagePagePropMigrationLegacyFormat for all wikis](https://gerrit.wikimedia.org/r/c/operations/mediawiki-config/+/839468)
- [SAL](https://sal.toolforge.org/log/JJu-rIMBa_6PSCT9rItY)
Maintenance script runs:
[x] [testwiki](https://sal.toolforge.org/log/Ka4-I4AB6FQ6iqKiXutl)
[x] [wikidataclient-test](https://sal.toolforge.org/log/zG8_I4AB8Fs0LHO59Kj7) (includes `testwiki` again with nothing to do this time)
[x] s4 / commonswiki
- [0-10M](https://sal.toolforge.org/log/PK5PI4AB6FQ6iqKiFffW)
- [10M-20M](https://sal.toolforge.org/log/nK9VI4AB6FQ6iqKi1wQe) (accidentally logged after the maintenance script is finished, rather than at the beginning like the other logs)
- [20M-30M](https://sal.toolforge.org/log/qv9WI4ABa_6PSCT9M_TH)
- [30M-40M](https://sal.toolforge.org/log/IK9aI4AB6FQ6iqKigwtD)
- [40M-50M](https://sal.toolforge.org/log/FwBeI4ABa_6PSCT98wOv)
- [50M-60M](https://sal.toolforge.org/log/Im9jI4AB8Fs0LHO5wdqn)
- [60M-70M](https://sal.toolforge.org/log/h29nI4AB8Fs0LHO5Ut9h)
- [70M-80M](https://sal.toolforge.org/log/dW9rI4AB8Fs0LHO5AuUI)
- [80M-90M](https://sal.toolforge.org/log/lgBvI4ABa_6PSCT9GhwZ)
- [90M-100M](https://sal.toolforge.org/log/dQBzI4ABa_6PSCT9HSA-)
- [100M-110M](https://sal.toolforge.org/log/W292I4AB8Fs0LHO57vBX)
- [110M-end](https://sal.toolforge.org/log/p296I4AB8Fs0LHO5cvZa)
[x] [s8 / wikidatawiki](https://sal.toolforge.org/log/OK-gI4AB6FQ6iqKivVVw)
[x] s5 & wikidataclient
[x] [cebwiki](https://sal.toolforge.org/log/pq-zI4AB6FQ6iqKioHov)
[x] [other sites](https://sal.toolforge.org/log/KK_kI4AB6FQ6iqKigKqv)
[x] s6 & wikidataclient
[x] [frwiki](https://sal.toolforge.org/log/uwCoI4ABa_6PSCT9hFPu)
[x] [jawiki](https://sal.toolforge.org/log/UXCrI4AB8Fs0LHO5XyuD)
[x] [ruwiki](https://sal.toolforge.org/log/jXCtI4AB8Fs0LHO5fTOJ)
[x] s7 & wikidataclient
[x] [viwiki](https://sal.toolforge.org/log/LXC3I4AB8Fs0LHO5_UUT)
[x] [metawiki](https://sal.toolforge.org/log/VnC6I4AB8Fs0LHO5N0aE)
[x] [other sites](https://sal.toolforge.org/log/cgDoI4ABa_6PSCT9AKl4)
[x] [s1 / enwiki](https://sal.toolforge.org/log/Ta-eI4AB6FQ6iqKi-lT1)
[x] [s2 & wikidataclient](https://sal.toolforge.org/log/26_VI4AB6FQ6iqKiYp-c)
[x] s3 & wikidataclient
[x] [ruwikinews](https://sal.toolforge.org/log/VHC_I4AB8Fs0LHO5g03d)
[x] [other sites](https://sal.toolforge.org/log/9nDhI4AB8Fs0LHO5r2-6)
Maintenance script runs, second round (inverted namespace):
- [arwiki](https://sal.toolforge.org/log/GCOOp4MB8Fs0LHO5ytio)
- [ruwikinews](https://sal.toolforge.org/log/ICOPp4MB8Fs0LHO5N9hR)
- [commonswiki](https://sal.toolforge.org/log/GiTAp4MB8Fs0LHO5_BjI)
- [enwiki](https://sal.toolforge.org/log/PFsqqIMB6FQ6iqKiZX7I)
- [zhwiki](https://sal.toolforge.org/log/8Vs_qIMB6FQ6iqKiK5p-)
- [ruwiktionary](https://sal.toolforge.org/log/qSiJrIMB8Fs0LHO57Lw_)
- [specieswiki](https://sal.toolforge.org/log/MV-JrIMB6FQ6iqKi9mUW)
- [cebwiki](https://sal.toolforge.org/log/rCiJrIMB8Fs0LHO5_7xq)
- [nlwiktionary, ruwiki, jawiki](https://sal.toolforge.org/log/lV-arIMB6FQ6iqKiTWxy)
- [viwiki, metawiki, frwiktionary](https://sal.toolforge.org/log/OZufrIMBa_6PSCT9tHcY)
- [itwiki, arzwiki, ptwiki](https://sal.toolforge.org/log/GSi2rIMB8Fs0LHO5DNQB)
- [all remaining wikis](https://sal.toolforge.org/log/lSjHrIMB8Fs0LHO5tN7l)