USE commonswiki_p;

SELECT NOW();

​

SELECT log_title, log_comment, log_user_text

FROM logging_logindex

WHERE log_type = 'block'

AND log_action = 'block'

AND log_comment REGEXP '[[:<:]](?:T129845|Z567|Z591|WP0|wikipedia (?:zero|0))[[:>:]]'

AND NOT log_title REGEXP '^[[:digit:]]{1,3}.[[:digit:]]{1,3}.[[:digit:]]{1,3}.[[:digit:]]{1,3}(?:/[[:digit:]]{1,2})?$'

AND NOT log_title 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 * FROM (

      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'

    ) wp0cats /* Using MATERIALIZED per https://stackoverflow.com/a/6157797 */

  )

) /* Not MATERIALIZED here. EXPLAINs:

MATERIALIZED:

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

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

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

|    1 | PRIMARY      | logging       | ref    | type_time                                                           | type_time        | 34      | const                             |     47067 | Using where              |

|    2 | MATERIALIZED | categorylinks | index  | PRIMARY,cl_timestamp,cl_sortkey,cl_collation_ext                    | cl_collation_ext | 296     | NULL                              | 269422751 | Using where; Using index |

|    2 | MATERIALIZED | page          | eq_ref | PRIMARY,name_title,page_random,page_len,page_redirect_namespace_len | PRIMARY          | 4       | commonswiki.categorylinks.cl_from |         1 | Using where              |

|    4 | MATERIALIZED | <derived5>    | ALL    | NULL                                                                | NULL             | NULL    | NULL                              |        23 |                          |

|    5 | DERIVED      | NULL          | NULL   | NULL                                                                | NULL             | NULL    | NULL                              |      NULL | No tables used           |

|    6 | UNION        | categorylinks | ref    | PRIMARY,cl_timestamp,cl_sortkey                                     | cl_sortkey       | 258     | const,const                       |        22 | Using where; Using index |

|    6 | UNION        | page          | eq_ref | PRIMARY                                                             | PRIMARY          | 4       | commonswiki.categorylinks.cl_from |         1 |                          |

| NULL | UNION RESULT | <union5,6>    | ALL    | NULL                                                                | NULL             | NULL    | NULL                              |      NULL |                          |

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

NOT MATERIALIZED:

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

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

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

|    1 | PRIMARY            | logging       | ref    | type_time                                                           | type_time  | 34      | const                                    | 47067 | 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 |

|    2 | DEPENDENT SUBQUERY | <subquery3>   | ALL    | distinct_key                                                        | NULL       | NULL    | NULL                                     |    23 |                          |

|    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              |

|    3 | MATERIALIZED       | <derived4>    | ALL    | NULL                                                                | NULL       | NULL    | NULL                                     |    23 |                          |

|    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 |                          |

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

*/

;