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
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)
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)
[] s5 & wikidataclient
[] s6 & wikidataclient
[] s7 & wikidataclient
[x] [s1 / enwiki](https://sal.toolforge.org/log/Ta-eI4AB6FQ6iqKi-lT1)
[] s2 & wikidataclient
[] s3 & wikidataclient