All articles not assigned to WikiProjects

Detects articles that are not assigned to any Wikiprojects, regardless of whether or not they have talk pages.

Note: deliberately ignores the special case of non-redirect pages with talk page redirects; these will be dealt with at the target talk page anyway

Example runs (note the decreasing trend; what is behind this?);

Executed in 1012.26 seconds as of Mon, 20 Mar 2023 09:58:13 UTC; Resultset (143194 rows) 
Executed in 1450.14 seconds as of Tue, 28 Mar 2023 22:46:50 UTC: Resultset (143064 rows) 

Found, and hopefully fixed, a bug where the WikiProject banner shell was causing false negatives.

Executed in 1206.02 seconds as of Sat, 16 Sep 2023 09:53:06 UTC. Resultset (94867 rows)
Executed in 1019.06 seconds as of Sun, 04 Feb 2024 08:20:57 UTC. Resultset (98871 rows)

SQL

SELECT
  article.page_title,
  article.page_id,
  talk.page_id AS talk_id
FROM
  page AS article
  LEFT JOIN page_props ON pp_page = article.page_id  AND pp_propname = 'disambiguation'
  LEFT JOIN page AS talk ON talk.page_title = article.page_title AND talk.page_namespace = 1 AND talk.page_is_redirect = 0
WHERE
  article.page_namespace = 0
  AND pp_page IS NULL
  AND article.page_is_redirect = 0
  AND NOT (article.page_title LIKE "List_of_%")
  AND NOT (article.page_title LIKE "Lists_of_%")
  AND NOT EXISTS (
    SELECT
      1
    FROM
      categorylinks
    WHERE
      article.page_id = cl_from
      AND (
        cl_to IN (
          "Temporary_maintenance_holdings",
          "Candidates_for_speedy_deletion",
          "Requested_RD1_redactions"
        )
        OR cl_to LIKE "%_names"
        OR cl_to LIKE "%_surnames"
        OR cl_to LIKE "Lists_of_%"
      )
  )
  AND NOT EXISTS (
    SELECT
      1
    FROM
      templatelinks
      JOIN linktarget ON lt_id = tl_target_id
    WHERE
      tl_from = article.page_id
      AND lt_namespace = 10
      AND (
        lt_title IN (
          "Wi",
          "Surname",
          "Given_name",
          "Animal_common_name",
          "Plant_common_name",
          "Nickname",
          "Dmbox"
        )
        OR lt_title LIKE "%index%"
        OR lt_title LIKE "%redirect%"
      )
  )
  AND (
    (talk.page_id IS NULL)
    OR (
      (talk.page_id IS NOT NULL)
      AND NOT EXISTS (
        SELECT
          1
        FROM
          categorylinks
        WHERE
          talk.page_id = cl_from
          AND (
            (
                cl_to LIKE "%WikiProject_%"
                AND NOT cl_to LIKE "%WikiProject_banner_shell%"
            )
            OR cl_to LIKE "%-Class_%_articles"
            OR cl_to LIKE "%-importance_%_articles"
            OR cl_to LIKE "%-priority_%_articles"
            OR cl_to LIKE "Unassessed_%_articles"
          )
      )
      AND NOT EXISTS (
        SELECT
          1
        FROM
          templatelinks
          JOIN linktarget ON lt_id = tl_target_id
        WHERE
          tl_from = talk.page_id
          AND lt_namespace = 10
          AND (lt_title LIKE "%WikiProject_%")
          AND NOT lt_title LIKE "%WikiProject_banner_shell%"
      )
    )
  )
ORDER BY
  article.page_title

SQL

Checking query status...