Untitled query #63841

SQL

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DROP TABLE IF EXISTS rep1;
DROP TABLE IF EXISTS rep2;

-- First, capture all potentially interesting stub templates
CREATE TABLE rep1 AS
SELECT page_title, page_is_redirect
FROM   enwiki_p.page
WHERE  page_namespace = 10 
AND    page_title LIKE '%-stub'
AND    page_is_redirect = 0;

-- Now count how many times each is transcluded
alter table rep1 add column trans int(8);

-- Process in chunks - repeat as necessary
UPDATE rep1
SET trans = (
  SELECT count(*)
  FROM   enwiki_p.templatelinks
  WHERE  tl_namespace = 10
  AND    tl_title = page_title )
WHERE trans IS NULL
LIMIT 5000;

alter table rep1 add index( redir );


-- Now find categories of interest
CREATE table rep2 AS
SELECT page_id, page_title 
FROM enwiki_p.page 
INNER JOIN enwiki_p.templatelinks ON tl_from = page_id 
WHERE tl_namespace = 10 
AND tl_title = "Stub_category" 
AND page_namespace = 14;

REPLACE INTO rep2
SELECT page_id, page_title 
FROM enwiki_p.page 
INNER JOIN enwiki_p.templatelinks ON tl_from = page_id 
WHERE tl_namespace = 10 
AND tl_title = "Parent-only_stub_category" 
AND page_namespace = 14;

REPLACE INTO rep2
SELECT page_id, page_title 
FROM enwiki_p.page 
INNER JOIN enwiki_p.templatelinks ON tl_from = page_id 
WHERE tl_namespace = 10 
AND tl_title = "Regional_stub_category" 
AND page_namespace = 14;

-- Index these
ALTER TABLE rep2 ADD INDEX( page_id );
ALTER TABLE rep1 ADD INDEX ( page_title );


-- Now count how many time each interesting template is linked from an interesting category
ALTER TABLE rep1 ADD COLUMN cats int(8);

-- Process in chunks - repeat as necessary
UPDATE rep1 r1
SET cats = (
  SELECT count(*)
  FROM enwiki_p.pagelinks l
  INNER JOIN rep2 r2 ON l.pl_from = r2.page_id
  WHERE l.pl_namespace = 10
  AND l.pl_title = r1.page_title )
WHERE Cats IS NULL
LIMIT 5000;

-- Now we need to credit any category links to redirects to
-- the targets of those redirects
UPDATE rep1 r1
INNER JOIN rep1 r2 ON r1.page_title = r2.redir	
SET r1.cats = r1.cats + r2.cats
WHERE r1.page_is_redirect = 0
AND   r2.page_is_redirect = 1;


-- Test the results
SELECT count(*) FROM rep1 WHERE page_is_redirect = 0 AND cats = 0 AND trans >= 60;

-- Generate some output
SELECT CONCAT( '*{{tl|', rep1.page_title, '}} - ', trans, ' transclusions' )
FROM rep1
WHERE page_is_redirect = 0
AND cats = 0
AND trans >= 50;
SQL

Checking query status...
