Biographical 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.

Note that this is far from exhaustive, but adding "X_people" for all X introduces too many false positives.

I've also tried the "People_(associated|educated)_(with|by)..." and "People_from_..." categories, and unfortunately they match groups, so I've removed thrm.

Example run: Executed in 762.82 seconds as of Sat, 25 Mar 2023 23:35:23 UTC. Resultset (2879 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"
               OR cl_to RLIKE "^(Date|Year)_of_(birth|death)_(missing|unknown).*$"
               OR cl_to RLIKE "^[0-9][^_]+_(births|deaths)$")) -- make sure we don't catch things like 'protest-related deaths'
  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...