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)