test2 - Quarry

SQL

SELECT CONCAT("[[",REPLACE(dab.page_title,"_"," "),"]]") AS "Disambig",
       DATE_FORMAT(rev_timestamp, "%Y-%m-%d")  AS "Last edited",
       CONCAT("",REPLACE(pl_title,"_"," "),"", IF(rd_from IS NULL, "", " (redirect)")) AS "Linked Person",
       DATE_FORMAT(cl_timestamp, "%Y-%m-%d")   AS "Cat added",
       cl_to                                   AS "Category"
FROM page AS dab
JOIN page_props    ON pp_page=dab.page_id AND pp_propname="disambiguation"
JOIN revision      ON rev_id=page_latest
JOIN pagelinks     ON pl_from=dab.page_id
JOIN page AS pl    ON pl.page_namespace=pl_namespace AND pl.page_title=pl_title
LEFT JOIN redirect ON rd_from=pl.page_id AND rd_fragment=""
LEFT JOIN page AS rd ON rd.page_namespace=rd_namespace AND rd.page_title=rd_title
JOIN categorylinks ON cl_from=IFNULL(rd.page_id, pl.page_id)
JOIN category      ON cat_title=cl_to
-- -- Optional: Use only principle links
-- JOIN u2815__p.principle_links ON pr_from=pl_from AND pr_title=pl.page_title AND 0=pl.page_namespace
--                 /* valid only for this version*/ AND pr_latest=dab.page_latest
WHERE cl_timestamp > rev_timestamp
AND dab.page_namespace=0 AND pl_namespace=0
-- Cats
AND cat_title LIKE "20__\_deaths"
-- -- If the dump is slightly old, avoid listing already done pages
-- AND rev_timestamp <= (SELECT DATE_FORMAT(IFNULL(@LastDumped, NOW()-INTERVAL 1 MONTH), "%Y%m%d%H%i%s"))
-- Investigate possible speedup
-- AND (pl.page_is_redirect=0 AND pl.page_links_updated
-- AND pl.page_links_updated >= DATE_FORMAT(NOW() - INTERVAL 3 MONTH, "%Y%m%d%H%i%s")
AND cl_timestamp >= NOW() - INTERVAL 6 MONTH /* cl_timestamp IS TYPE timestamp! */
AND (
   SUBSTRING(cat_title, 1, 4) > rev_timestamp
/* DAB was edited this year */
OR DATE_FORMAT("%Y", NOW()) < rev_timestamp AND rev_timestamp + INTERVAL 30 DAY < cl_timestamp AND cl_timestamp < NOW() - INTERVAL 7 DAY
)
ORDER BY rev_timestamp DESC;
SQL

Checking query status...
