I have a query that tries to find pages in article namespace that contain a link to some page in the user/user talk namespace. Many of these result from users signing in the articles (which is obviously not appropriate). The query also tries to exclude pages that have a legitimate link to user pages; e.g. our {{under construction}} template contains a link to the user page of the person who affixed the template, and we can easily find these pages based on a category they get added to by the same template.

The query is very brief, and I have pasted it below. To the best of my understand, it uses indexes and does not cause any USING WHERE or USING FILESORT steps. Nevertheless, the query takes more than 30 minutes on Labs and is killed as a result.

Is there a way to optimize this query further? If not, that is okay; I will implement a two-step query. But I just want to make sure I'm not missing something obvious.

Thanks,

Huji

SELECT
  page_title,
  pl_title,
  CASE
    WHEN c2.cl_to IS NULL THEN NULL
    ELSE '{{yes}}'
  END AS under_construction
FROM page

JOIN pagelinks
  ON page_id = pl_from
LEFT JOIN categorylinks c2
  ON page_id = c2.cl_from
  AND c2.cl_to = 'صفحه‌های_گسترده_در_دست_ساخت'
WHERE
  page_namespace = 0
  AND pl_namespace IN (2, 3)