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
```
----
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)