Links to redirecting file pages - local

SQL

USE dewiki_p;
SET @lenw = LENGTH('https://de.wikipedia.org/wiki/');
SET @lenc = LENGTH('https://commons.wikimedia.org/wiki/');
SELECT now() AS timestamp;
#Wikipedia local
(SELECT CONCAT('# [[', IF (wp.page_namespace = 14, ':Kategorie:', ''), REPLACE (wp.page_title, '_', ' '), ']] ',
               '• [{{fullurl:', IF (wp.page_namespace = 14, 'Kategorie:', ''), REPLACE (wp.page_title, '_', ' '), '|action=edit}} edit] ',
               '→ [[:Datei:', REPLACE (pl_title, '_', ' '), ']]') AS wikipage
FROM page wp
INNER JOIN pagelinks ON pl_from = wp.page_id AND pl_from_namespace IN (0, 14) AND pl_namespace = 6
INNER JOIN page cp ON cp.page_title = pl_title AND cp.page_namespace = 6 AND cp.page_is_redirect = 1
WHERE wp.page_namespace IN (0, 14)
) UNION
#interwiki to Commons
(SELECT CONCAT('# [[', IF (wp.page_namespace = 14, ':{{ns:14}}:', ''), REPLACE (wp.page_title, '_', ' '), ']] ',
               '• [{{fullurl:', IF (wp.page_namespace = 14, '{{ns:14}}:', ''), REPLACE (wp.page_title, '_', ' '), '|action=edit}} edit] ',
               '→ [[c:File:', REPLACE (iwl_title, '_', ' '), ']]') AS wpage
FROM iwlinks
INNER JOIN page wp
ON wp.page_id = iwl_from
AND wp.page_namespace IN (0, 14)
INNER JOIN commonswiki_p.page cp
ON cp.page_title = iwl_title
AND cp.page_namespace = 6
AND cp.page_is_redirect = 1
WHERE iwl_prefix IN ('c', 'commons')
) UNION
#external to Commons
(SELECT CONCAT('# [[', IF (wp.page_namespace = 14, ':{{ns:14}}:', ''), REPLACE (wp.page_title, '_', ' '), ']] ',
               '• [{{fullurl:', IF (wp.page_namespace = 14, '{{ns:14}}:', ''), REPLACE (wp.page_title, '_', ' '), '|action=edit}} edit] ',
               '→ [[c:', REPLACE (cp.page_title, '_', ' '), ']]') AS wpage
FROM externallinks
INNER JOIN page wp
ON wp.page_id = el_from
AND wp.page_namespace IN (0, 14)
INNER JOIN commonswiki_p.page cp
ON cp.page_title = SUBSTRING(REPLACE (el_to, CONCAT('?uselang=', @l), ''), IF ((LEFT (el_to, 5) = 'https'), @lenc + 1, @lenc))
AND cp.page_namespace = 6
AND cp.page_is_redirect = 1
WHERE ((el_to LIKE 'https://commons.wikimedia.org/wiki/%') OR (el_to LIKE 'http://commons.wikimedia.org/wiki/%'))
#AND el_to NOT LIKE '%//commons.wikimedia.org/wiki/Category:%'
AND el_to NOT LIKE '%\%%' #temp
)
LIMIT 100;
SQL

Checking query status...
