From: Dan Collins <EN.WP.ST47@gmail.com>

Date: Tue, 18 Mar 2008 10:38:33

http://tools.wikimedia.org/~st47/DBQ16

From: MZMcBride <mzmcbride@gmail.com>

Date: Wed, 09 Apr 2008 00:17:40

Would it be possible to have this query re-run using all namespaces instead of just NS:1?

Also, if the person who runs the query could post the query they use, that'd be great. It'd certainly help SQL newbs (like me!) learn. ![][1] And, I can put it on the TS wiki for future reference.

Cheers.

[1]: https://jira.toolserver.org/images/icons/emoticons/smile.gif

From: Kylu <kylu@ts.wikimedia.org>

Date: Sun, 13 Apr 2008 21:34:04

ns:1 select count![][1]/page_title,rd_title/ from page,redirect where page_id=rd_from and page_namespace=1 and rd_namespace=1 and (select count![][1] from pagelinks where pl_namespace=page_namespace and pl_title=page_title)=0;

other enwiki_p talkpage namespaces: 3,5,7,9,11,15,101

Will post to https://wiki.ts.wikimedia.org/view/Query_service/Unused_talk_redirects

• Update 17 Apr 2008: Not done. TS Admins note that the query, as written, uses too many resources. Try converting the query you gave me to JOIN and reissue.
  
  [1]: https://jira.toolserver.org/images/icons/emoticons/star_yellow.gif

From: SQL <sxwiki@gmail.com>

Date: Fri, 09 May 2008 13:27:28

I've added a query that looks like it worked to me, output at http://tools.wikimedia.de/~sql/mz3.txt.gz .

SELECT CONCAT(ns_name, ':', page_title) FROM page
JOIN redirect ON page_id=rd_from
JOIN toolserver.namespace ON dbname='enwiki_p' AND ns_id = page_namespace
WHERE page_namespace=1 OR page_namespace=3 OR page_namespace=5 OR page_namespace=7 OR page_namespace=9 OR page_namespace=11 OR page_namespace=15 OR
page_namespace=101
AND rd_namespace=1 OR rd_namespace=3 OR rd_namespace=5 OR rd_namespace=7 OR rd_namespace=9 OR rd_namespace=11 OR rd_namespace=15 OR rd_namespace=101
AND (SELECT count![][1] FROM pagelinks WHERE pl_namespace=page_namespace AND pl_title=page_title)=0;

Execution took appx 8mins.

[1]: https://jira.toolserver.org/images/icons/emoticons/star_yellow.gif

From: SQL <sxwiki@gmail.com>

Date: Sat, 10 May 2008 05:34:25

Requester was satisfied with my results, consented to close this issue.

This bug was imported as RESOLVED. The original assignee has therefore not been
set, and the original reporters/responders have not been added as CC, to
prevent bugspam.

If you re-open this bug, please consider adding these people to the CC list:
Original assignee: (none)
CC list: b@mzmcbride.com, sxwiki@gmail.com, EN.WP.ST47@gmail.com