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)