-- First generate a list of applicable titles
DROP TABLE IF EXISTS redir_titles;

CREATE TABLE redir_titles (
      rt_id int(8) unsigned NOT NULL,
      rt_title varchar(255) BINARY NOT NULL,
      rt_crushed varchar(255) BINARY,

      PRIMARY KEY (rt_id)
);


INSERT INTO redir_titles ( rt_id, rt_title )
SELECT /* SLOW_OK */ page_id, page_title
FROM enwiki_p.page
WHERE page_namespace = 0
AND  length( page_title ) >= 10
AND  page_is_redirect = 1;

UPDATE redir_titles
SET  rt_crushed = lcase( rt_title )
WHERE rt_title != lcase( rt_title );

DELETE FROM redir_titles WHERE rt_crushed IS NULL;

ALTER TABLE redir_titles ADD INDEX( rt_crushed );

-- Now find pairs of similar redirects

DROP TABLE IF EXISTS similar_redirects;

CREATE TABLE similar_redirects (
      sr_src_id1 int(8) unsigned NOT NULL,
      sr_src_title1 varchar(255) binary NOT NULL,
	  sr_targ_ns1 int(11),
	  sr_targ_title1 varchar(255) binary,
	  
      sr_src_id2 int(8) unsigned NOT NULL,
      sr_src_title2 varchar(255) binary NOT NULL,
	  sr_targ_ns2 int(11),
	  sr_targ_title2 varchar(255) binary,
	  
      PRIMARY KEY (sr_src_id1,sr_src_id2)
);


-- This will have found most pairs in both directions (A v B / B v A) - eliminate duplicates of this type
-- by checking the A id is always less than the B.

INSERT INTO similar_redirects( sr_src_id1, sr_src_title1, sr_src_id2, sr_src_title2 )
SELECT /* SLOW_OK */ a.rt_id, a.rt_title, b.rt_id, b.rt_title
FROM   redir_titles a
INNER JOIN  redir_titles b ON a.rt_crushed = b.rt_crushed
WHERE a.rt_id < b.rt_id;


-- Fill in the redirect target for each of these and dismiss any with the same target
-- Doing it this way is ugly, but is SQL language neutral.  Go standardisation!

UPDATE similar_redirects
SET sr_targ_ns1 = ( SELECT rd_namespace FROM enwiki_p.redirect WHERE rd_from = sr_src_id1 );

UPDATE similar_redirects
SET sr_targ_title1 = ( SELECT rd_title FROM enwiki_p.redirect WHERE rd_from = sr_src_id1 );

UPDATE similar_redirects
SET sr_targ_ns2 = ( SELECT rd_namespace FROM enwiki_p.redirect WHERE rd_from = sr_src_id2 );

UPDATE similar_redirects
SET sr_targ_title2 = ( SELECT rd_title FROM enwiki_p.redirect WHERE rd_from = sr_src_id2 );

DELETE
FROM similar_redirects
WHERE sr_targ_ns1 = sr_targ_ns2
AND   sr_targ_title1 = sr_targ_title2;

-- See what we have left

SELECT count(*) FROM similar_redirects;

SELECT concat( '* [[' , sr_src_title1, ']] redirects to [[', sr_targ_title1, ']], but [[', sr_src_title2, ']] redirects to [[', sr_targ_title2, ']]' )
FROM similar_redirects
WHERE  sr_targ_ns1 = 0
AND    sr_targ_ns2 = 0
ORDER by sr_src_title1 ASC
LIMIT 200;