Topic on Talk:Quarry

I'm trying to find pages which a given user has deleted, and were subsequently restored (by anybody). This seems like it should be the right query:

select count(*)

from logging_userindex lu

join logging_logindex ll on lu.log_page = ll.log_page

join actor on lu.log_actor = actor_id

where actor_name = 'RoySmith'

and lu.log_type = 'delete'

and lu.log_action = 'delete'

and ll.log_type = 'delete'

and ll.log_action = 'restore'

;

but it's taking forever to run. I'm assuming it's the self-join on logging that's the problem. Is there a better way to do this?

EXPLAIN:

+------+-------------+---------------------+-------+----------------------------------------------------------------------------+---------------------+---------+------------------------+-----------+------------------------------------+
| id   | select_type | table               | type  | possible_keys                                                              | key                 | key_len | ref                    | rows      | Extra                              |
+------+-------------+---------------------+-------+----------------------------------------------------------------------------+---------------------+---------+------------------------+-----------+------------------------------------+
|    1 | PRIMARY     | actor               | const | PRIMARY,actor_name                                                         | actor_name          | 257     | const                  |         1 |                                    |
|    1 | PRIMARY     | logging             | ref   | type_time,actor_time,log_actor_type_time,log_actor_deleted,log_type_action | log_actor_type_time | 42      | const,const            |     12316 | Using index condition; Using where |
|    1 | PRIMARY     | logging             | ref   | type_time,log_page_id_time,log_type_action                                 | log_page_id_time    | 5       | func                   |         2 | Using index condition; Using where |
|   13 | SUBQUERY    | revision            | ALL   | PRIMARY                                                                    | NULL                | NULL    | NULL                   | 778735536 | Using where                        |
|   13 | SUBQUERY    | revision_actor_temp | ref   | PRIMARY,revactor_rev                                                       | PRIMARY             | 4       | enwiki.revision.rev_id |         1 | Using index                        |
|   12 | SUBQUERY    | logging             | range | type_time,actor_time,log_actor_type_time,log_actor_deleted,log_type_action | log_actor_type_time | 42      | NULL                   |     15784 | Using index condition; Using where |
|   11 | SUBQUERY    | recentchanges       | ref   | rc_actor,rc_actor_deleted                                                  | rc_actor_deleted    | 8       | const                  |       878 | Using where; Using index           |
|   10 | SUBQUERY    | filearchive         | ref   | fa_actor_timestamp,fa_actor_deleted                                        | fa_actor_deleted    | 8       | const                  |        15 | Using where; Using index           |
|    9 | SUBQUERY    | oldimage            | ref   | oi_actor_timestamp,oi_actor_deleted                                        | oi_actor_deleted    | 8       | const                  |         1 | Using where; Using index           |
|    8 | SUBQUERY    | image               | ref   | img_actor_timestamp                                                        | img_actor_timestamp | 8       | const                  |        14 | Using index                        |
|    7 | SUBQUERY    | ipblocks            | ref   | ipb_actor_deleted                                                          | ipb_actor_deleted   | 9       | const,const            |       317 | Using index                        |
|    6 | SUBQUERY    | archive             | ref   | ar_actor_timestamp,ar_actor_deleted                                        | ar_actor_deleted    | 8       | const                  |      3268 | Using where; Using index           |
|    5 | SUBQUERY    | user                | const | PRIMARY                                                                    | PRIMARY             | 4       | const                  |         1 | Using index                        |
+------+-------------+---------------------+-------+----------------------------------------------------------------------------+---------------------+---------+------------------------+-----------+------------------------------------+

The most expensive subquery is the ALL on revision, which is a result from the view actor.

A simple fix is with https://quarry.wmflabs.org/query/41110:

+------+-------------+---------+-------+----------------------------------------------------------------------------+---------------------+---------+-------------+-------+------------------------------------+
| id   | select_type | table   | type  | possible_keys                                                              | key                 | key_len | ref         | rows  | Extra                              |
+------+-------------+---------+-------+----------------------------------------------------------------------------+---------------------+---------+-------------+-------+------------------------------------+
|    1 | PRIMARY     | actor   | const | PRIMARY,actor_name                                                         | actor_name          | 257     | const       |     1 | Using index                        |
|    1 | PRIMARY     | logging | ref   | type_time,actor_time,log_actor_type_time,log_actor_deleted,log_type_action | log_actor_type_time | 42      | const,const | 12316 | Using index condition; Using where |
|    1 | PRIMARY     | logging | ref   | type_time,log_page_id_time,log_type_action                                 | log_page_id_time    | 5       | func        |     2 | Using index condition; Using where |
|    5 | SUBQUERY    | logging | range | type_time,actor_time,log_actor_type_time,log_actor_deleted,log_type_action | log_actor_type_time | 42      | NULL        | 15784 | Using index condition; Using where |
+------+-------------+---------+-------+----------------------------------------------------------------------------+---------------------+---------+-------------+-------+------------------------------------+

Looks like it is still too slow :(

https://quarry.wmflabs.org/query/41115

The log_page is always NULL for deleted pages so you have to join on namespace & title.

https://quarry.wmflabs.org/query/41118

3.33 seconds.