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 
  DISTINCT CONCAT("* [[",article.page_title, "]]")
FROM
  page AS article
  INNER JOIN page AS talk ON talk.page_title = article.page_title
WHERE
 -- article.page_id % 10 = 2
  article.page_namespace = 0
  AND talk.page_namespace = 1
  AND article.page_is_redirect = 0
  AND talk.page_is_redirect = 0
  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%"))
  AND EXISTS (SELECT 1 FROM categorylinks WHERE cl_from = article.page_id and cl_to = "Living_people")
  ORDER BY article.page_title

SQL

Checking query status...