Living people articles without WP Biography tag

playing around with the query scheme to see what effect things have on performance; a bit slower with the redundant sub-SELECT, but not much. Repeating to see whether there's some sort of schema optimization going on.

Now producing wiki markup so this can be easily cut-and-pasted into WP

Now uses LEFT JOIN so I can deal with both missing talk pages and talk pages missing categories in a single query. Had to make sure query term nesting was got right, as otherwise it selects all articles without talk pages. There is probably a neater way of doing this, but this at least works.

This includes only articles with the "Living people" category.

Sample run: Executed in 207.21 seconds as of Thu, 06 Apr 2023 14:49:58 UTC. Resultset (1195 rows)

SQL

SELECT 
  CONCAT("* [[",article.page_title, "]]") -- , talk.page_id AS talk_page_id
FROM
  page AS article
  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_id % 10 = 2
  article.page_namespace = 0
  AND article.page_is_redirect = 0
  AND NOT article.page_title RLIKE "^(Lists?_of|[0-9]{4})_.*$" -- no dated events or lists
  AND EXISTS (SELECT 1 FROM categorylinks WHERE cl_from = article.page_id and 
              (cl_to = "Living_people"
)) 
  AND (
    (talk.page_id IS NULL) 
    OR ((talk.page_id IS NOT NULL) AND NOT EXISTS (SELECT 1 FROM categorylinks WHERE cl_from = talk.page_id 
                 and (cl_to LIKE "%Biography_articles" 
                      OR cl_to LIKE "%biography_articles" 
                      OR cl_to LIKE "%WikiProject_Biography%"))))

ORDER BY article.page_title

SQL

Checking query status...