Untitled query #63531

SQL

SET @pat='(?i).*p.*ri.*ch.*';

SELECT CONCAT('[[', CASE page_namespace
                      WHEN -2 THEN 'Media:'               WHEN -1 THEN 'Special:'
                      WHEN 0 THEN ''                      WHEN 1 THEN 'Talk:'
                      WHEN 2 THEN 'User:'                 WHEN 3 THEN 'User talk:'
                      WHEN 4 THEN 'Wikipedia:'            WHEN 5 THEN 'Wikipedia talk:'
                      WHEN 6 THEN ':File:'                WHEN 7 THEN 'File talk:'
                      WHEN 8 THEN 'MediaWiki:'            WHEN 9 THEN 'MediaWiki talk:'
                      WHEN 10 THEN 'Template:'            WHEN 11 THEN 'Template talk:'
                      WHEN 12 THEN 'Help:'                WHEN 13 THEN 'Help talk:'
                      WHEN 14 THEN ':Category:'           WHEN 15 THEN 'Category talk:'
                      WHEN 100 THEN 'Portal:'             WHEN 101 THEN 'Portal talk:'
                      WHEN 118 THEN 'Draft:'              WHEN 119 THEN 'Draft talk:'
                      WHEN 710 THEN 'TimedText:'          WHEN 711 THEN 'TimedText talk:'
                      WHEN 828 THEN 'Module:'             WHEN 829 THEN 'Module talk:'
                      WHEN 2300 THEN 'Gadget:'            WHEN 2301 THEN 'Gadget talk:'
                      WHEN 2302 THEN 'Gadget definition:' WHEN 2303 THEN 'Gadget definition talk:'
                      ELSE CONCAT('{{ns:', page_namespace, '}}:')
                    END, 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
GROUP BY page_namespace, page_title
ORDER BY `existing` DESC, page_namespace ASC, page_title ASC;
SQL

Checking query status...
