CSD G8 subpages watchlist

SQL

with recursive occur_index as (
  select length(page_title) - length(replace(page_title, '/', '')) as occur,
  page_id, page_namespace, page_title, page_is_redirect from page where page_namespace in (1)
),
parent_index (child, parent, occur, page_namespace) as (
  select page_title, substring_index(page_title, '/', occur), occur, page_namespace from occur_index where occur != 0
  union distinct
  select parent, substring_index(parent, '/', occur - 1), occur - 1, page_namespace from parent_index where occur > 1
),
search (candidate, occur, page_namespace) as (
  select distinct parent, 0, page_namespace from parent_index where occur = 1
  and parent not in (
    select page_title from occur_index where occur = 0 and page_namespace = parent_index.page_namespace
  )
  union distinct
  select child, search.occur + 1, search.page_namespace from search
  join parent_index on candidate = parent
  and search.occur = parent_index.occur - 1 and search.page_namespace = parent_index.page_namespace
  where candidate not in (
    select page_title from occur_index where occur = search.occur and page_namespace = search.page_namespace
  )
)

select occur_index.page_namespace, page_title from occur_index
join search on page_title = candidate and occur_index.page_namespace = search.page_namespace
where page_is_redirect = 0 and page_id not in (
  select cl_from from categorylinks where cl_to in ('Candidates_for_speedy_deletion')
)
/*
and (occur_index.page_namespace % 2 = 0 or page_title not in (
  select page_title from page where page_namespace = occur_index.page_namespace - 1
))
and page_id not in (
  select tl_from from templatelinks where tl_target_id in (
    select lt_id from linktarget where lt_title in ('Archive', 'Automatic_archive_navigator') and lt_namespace = 10
  )
)
*/
and page_title rlike '/to\_do$'
-- and page_title rlike 'Archive' collate utf8_general_ci
-- and page_title not rlike '(/TDs$|CDU/CSU|North/South|elections?/|Rep/)'
SQL

Checking query status...
