WP0 blocked users without category (ipblocks, no materialization)

Fork of WP0 blocked users without category (ipblocks) by Zhuyifei1999
This query is marked as a draft This query has been published by Zhuyifei1999.
SQL

USE commonswiki_p;
SELECT NOW();

SET SESSION optimizer_switch='materialization=off';

SELECT ipb_address, ipb_reason, ipb_by_text
FROM ipblocks
WHERE ipb_reason REGEXP '[[:<:]](?:T129845|Z567|Z591|WP0|wikipedia (?:zero|0))[[:>:]]'
AND ipb_user != 0
AND NOT REPLACE(ipb_address, ' ', '_') IN (
  SELECT page_title
  FROM page
  INNER JOIN categorylinks
  ON cl_from = page_id
  WHERE cl_type = "page"
  AND page_namespace = 2
  AND cl_to IN (
    SELECT 'Users_suspected_of_abusing_Wikipedia_Zero' AS catname
    UNION
    SELECT page_title AS catname
    FROM page
    INNER JOIN categorylinks
    ON cl_from = page_id
    WHERE cl_type = 'subcat'
    AND cl_to = 'Users_suspected_of_abusing_Wikipedia_Zero'
  )
) /* EXPLAINs:
This version, without query 20392's SELECT * FROM:
+------+--------------------+---------------+--------+---------------------------------------------------------------------+------------+---------+-----------------------------------+---------+-------------------------------------------------+
| id   | select_type        | table         | type   | possible_keys                                                       | key        | key_len | ref                               | rows    | Extra                                           |
+------+--------------------+---------------+--------+---------------------------------------------------------------------+------------+---------+-----------------------------------+---------+-------------------------------------------------+
|    1 | PRIMARY            | ipblocks      | ALL    | ipb_user                                                            | NULL       | NULL    | NULL                              |  107088 | Using where                                     |
|    2 | DEPENDENT SUBQUERY | page          | eq_ref | PRIMARY,name_title,page_random,page_len,page_redirect_namespace_len | name_title | 261     | const,func                        |       1 | Using where; Using index; Full scan on NULL key |
|    2 | DEPENDENT SUBQUERY | categorylinks | ref    | PRIMARY,cl_timestamp,cl_sortkey,cl_collation_ext                    | PRIMARY    | 4       | commonswiki.page.page_id          | 2698321 | Using where                                     |
|    3 | DEPENDENT SUBQUERY | NULL          | NULL   | NULL                                                                | NULL       | NULL    | NULL                              |    NULL | No tables used                                  |
|    4 | DEPENDENT UNION    | categorylinks | ref    | PRIMARY,cl_timestamp,cl_sortkey,cl_collation_ext                    | cl_sortkey | 258     | const,const                       |      22 | Using where; Using index                        |
|    4 | DEPENDENT UNION    | page          | eq_ref | PRIMARY,page_random,page_len,page_redirect_namespace_len            | PRIMARY    | 4       | commonswiki.categorylinks.cl_from |       1 | Using where                                     |
| NULL | UNION RESULT       | <union3,4>    | ALL    | NULL                                                                | NULL       | NULL    | NULL                              |    NULL |                                                 |
+------+--------------------+---------------+--------+---------------------------------------------------------------------+------------+---------+-----------------------------------+---------+-------------------------------------------------+
With SELECT * FROM, but with materialization=off:
+------+--------------------+---------------+--------+---------------------------------------------------------------------+------------+---------+------------------------------------------+--------+-----------------------------------------------------+
| id   | select_type        | table         | type   | possible_keys                                                       | key        | key_len | ref                                      | rows   | Extra                                               |
+------+--------------------+---------------+--------+---------------------------------------------------------------------+------------+---------+------------------------------------------+--------+-----------------------------------------------------+
|    1 | PRIMARY            | ipblocks      | ALL    | ipb_user                                                            | NULL       | NULL    | NULL                                     | 107088 | Using where                                         |
|    2 | DEPENDENT SUBQUERY | page          | eq_ref | PRIMARY,name_title,page_random,page_len,page_redirect_namespace_len | name_title | 261     | const,func                               |      1 | Using where; Using index; Full scan on NULL key     |
|    2 | DEPENDENT SUBQUERY | <derived4>    | ALL    | NULL                                                                | NULL       | NULL    | NULL                                     |     23 | Start temporary; Using join buffer (flat, BNL join) |
|    2 | DEPENDENT SUBQUERY | categorylinks | eq_ref | PRIMARY,cl_timestamp,cl_sortkey,cl_collation_ext                    | PRIMARY    | 261     | commonswiki.page.page_id,wp0cats.catname |      1 | Using where; End temporary                          |
|    4 | DERIVED            | NULL          | NULL   | NULL                                                                | NULL       | NULL    | NULL                                     |   NULL | No tables used                                      |
|    5 | UNION              | categorylinks | ref    | PRIMARY,cl_timestamp,cl_sortkey                                     | cl_sortkey | 258     | const,const                              |     22 | Using where; Using index                            |
|    5 | UNION              | page          | eq_ref | PRIMARY                                                             | PRIMARY    | 4       | commonswiki.categorylinks.cl_from        |      1 |                                                     |
| NULL | UNION RESULT       | <union4,5>    | ALL    | NULL                                                                | NULL       | NULL    | NULL                                     |   NULL |                                                     |
+------+--------------------+---------------+--------+---------------------------------------------------------------------+------------+---------+------------------------------------------+--------+-----------------------------------------------------+

As long as 'materialization=off' the query speed seem to be similar and difficult to be comparied (<1 second).
*/
;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
Submit Query Stop Query
All SQL code is licensed under CC0 License.
SQL

Checking query status...
