Untitled query #33692

SQL

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                                     |
+------+--------------------+---------------+-----------------+---------------------------------------------------+------------+---------+---------------------------------------+----------+-------------------------------------------------+
*/
SQL

Checking query status...
