USE commonswiki_p;

​

SET SESSION optimizer_switch='materialization=off';

​

SELECT concat('File:', p.page_title) AS fqp

    FROM page p

LEFT JOIN templatelinks

    ON tl_title IN ('Uncategorized', 'Uncategorized-Geograph', 'Check_categories')

    AND tl_namespace = 10

    AND page_id = tl_from

LEFT JOIN categorylinks

    ON page_id = cl_from

    AND (

        SELECT page_id AS id

        FROM page ppp

        WHERE ppp.page_namespace = 14

            AND ppp.page_title = cl_to

    ) NOT IN (

        SELECT DISTINCT pp_page

        FROM page_props

        WHERE pp_propname IN ('hiddencat')

    )

WHERE p.page_namespace = 6

    AND cl_from IS NULL

    AND tl_from IS NULL

    AND page_is_redirect = 0

    -- group by p.page_id

    -- order by p.page_latest asc

;

​

/*

+------+--------------------+---------------+-----------------+---------------------------------------------------+------------+---------+---------------------------------------+----------+-------------------------------------------------+

| id   | select_type        | table         | type            | possible_keys                                     | key        | key_len | ref                                   | rows     | Extra                                           |

+------+--------------------+---------------+-----------------+---------------------------------------------------+------------+---------+---------------------------------------+----------+-------------------------------------------------+

|    1 | PRIMARY            | page          | ref             | name_title,page_redirect_namespace_len            | name_title | 4       | const                                 | 35836553 | Using where                                     |

|    1 | PRIMARY            | templatelinks | ref             | PRIMARY,tl_namespace                              | PRIMARY    | 8       | commonswiki.page.page_id,const        |        7 | Using where; Using index; Not exists            |

|    1 | PRIMARY            | categorylinks | ref             | PRIMARY                                           | PRIMARY    | 4       | commonswiki.page.page_id              |        2 | Using where; Using index; Not exists            |

|    3 | DEPENDENT SUBQUERY | page_props    | unique_subquery | PRIMARY,pp_propname_page,pp_propname_sortkey_page | PRIMARY    | 66      | func,const                            |        1 | Using index; Using where; Full scan on NULL key |

|    2 | DEPENDENT SUBQUERY | page          | eq_ref          | name_title                                        | name_title | 261     | const,commonswiki.categorylinks.cl_to |        1 | Using index                                     |

+------+--------------------+---------------+-----------------+---------------------------------------------------+------------+---------+---------------------------------------+----------+-------------------------------------------------+

*/