Toolserver:Query service/16

This page was moved from the Toolserver wiki.
Toolserver has been replaced by Toolforge. As such, the instructions here may no longer work, but may still be of historical interest.
Please help by updating examples, links, template links, etc. If a page is still relevant, move it to a normal title and leave a redirect.

Talk page redirects without incoming links

Description	Number of and listing of unreferenced talkpage redirects on enwiki.
Assignee	ST47, Kylu
JIRA bug	DBQ-16
Source	This page
Results	This page, subpage
Approximate run time	count: 5 minutes, listing:
Interval	on demand

Category:Query service

Counts

Count (main namespace only)

Count query (Werdna):

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

Result:

+----------+
| count(*) |
+----------+
|     1459 |
+----------+
1 row in set (4 min 34.17 sec)

Count (all namespaces)

Count query (Kylu):

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

Result:

Results

Results (all namespaces)

Query (Kylu):

select /* count(*) */ toolserver.namespace,page_title,rd_title from page,redirect join toolserver.namespace where page_id=rd_from /* and page_namespace=1 and rd_namespace=1 */ and toolserver.namespace = page_namespace and (select count(*) from pagelinks where pl_namespace=page_namespace and pl_title=page_title)=0;

Result:

Not done, runtime > 1.5Hrs.

Query (SQL -- Sorta frankenstiened the above...)

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(*) from pagelinks where pl_namespace=page_namespace and pl_title=page_title)=0;

Result:

http://tools.wikimedia.de/~sql/mz3.txt.gz

real	7m7.967s
user	0m0.040s
sys	0m0.020s