Obsolete:Tool:Query service/16

From Wikitech
Jump to navigation Jump to search

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


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

This page was moved from toolserver:en:Query service/16. It's edit history can be viewed at Nova Resource:Tools/Tools/Query service/16/edithistory