SET @pat='(?i).*b\.?f\.?d\.?i.*';

​

WITH ns(ns_n, ns_s) AS (VALUES (-2, 'Media:'), (-1, 'Special:'), (0, ''), (1, 'Talk:'), (2, 'User:'), (3, 'User talk:'), (4,

  'Wikipedia:'), (5, 'Wikipedia talk:'), (6, ':File:'), (7, 'File talk:'), (8, 'MediaWiki:'), (9, 'MediaWiki talk:'), (10, 'Template:'),

  (11, 'Template talk:'), (12, 'Help:'), (13, 'Help talk:'), (14, ':Category:'), (15, 'Category talk:'), (100, 'Portal:'), (101,

  'Portal talk:'), (118, 'Draft:'), (119, 'Draft talk:'), (126, 'MOS:'), (127, 'MOS talk:'), (710, 'TimedText:'), (711, 'TimedText talk:'),

  (828, 'Module:'), (829, 'Module talk:'))

SELECT CONCAT('[[',

              COALESCE(ns_s, CONCAT('{{ns:', page_namespace, '}}:')),

              REPLACE(page_title, '_', ' '),

              ']]') AS title,

       EXISTS (SELECT 1

               FROM page p2

               WHERE p2.page_namespace = sq.page_namespace

                 AND p2.page_title = sq.page_title) AS existing,

       COUNT(log_namespace) AS `times deleted`,

       GROUP_CONCAT(log_timestamp SEPARATOR ' // ') AS `deletion timestamps`,

       GROUP_CONCAT(comment_text SEPARATOR ' // ') AS `deletion comments`,

       (SELECT GROUP_CONCAT(comment_text SEPARATOR ' // ')

        FROM logging_logindex

        JOIN comment_logging ON comment_id = log_comment_id

        WHERE log_namespace = page_namespace

          AND log_title = page_title

          AND log_type = 'protect'

          AND log_action = 'protect'

          AND log_params LIKE '%create=%') AS `salt comments`

FROM (SELECT page_namespace, page_title

      FROM page

      WHERE REPLACE(page_title, '_', ' ') RLIKE @pat

​

      UNION DISTINCT

​

      SELECT ar_namespace, ar_title

      FROM archive

      WHERE REPLACE(ar_title, '_', ' ') RLIKE @pat) sq

LEFT JOIN logging_logindex

       ON log_namespace = page_namespace

      AND log_title = page_title

      AND log_action = 'delete'

      AND log_type = 'delete'

LEFT JOIN comment_logging ON comment_id = log_comment_id

LEFT JOIN ns ON ns_n = page_namespace

GROUP BY page_namespace, page_title

ORDER BY `existing` DESC, page_namespace ASC, page_title ASC;