Page MenuHomePhabricator

Compose query for minor edit history
Closed, ResolvedPublic1 Estimated Story Points

Description

Per user story T234942: Curator gets minor edit history, we want to add a filter for minor edit history as one of the available filter types on the existing /page/{title}/history endpoint.

Create an SQL query to retrieve this history. Update this task with the query. Incorporation of the query into the existing endpoint will be performed under a separate task.

This may be somewhat related to T235572: Compose query for minor edit count

Requirements:

  • Task updated with query
  • Check performance of query
    • If performance is an issue contact DBAs to determine next steps

Event Timeline

BPirkle created this task.Oct 16 2019, 2:21 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptOct 16 2019, 2:21 PM

For reference, count queries for the existing filter types were created under T231599: Compose New History Queries and implemented under T231597: Implement GET History Filter

In meeting notes, @Pchelolo said "There's a rev_minor_edit field in the revision table, but there's no index on it, so it might be really slow...".

What (if anything) do we need to do with the rev_deleted field in this query?

WDoranWMF triaged this task as Medium priority.Oct 16 2019, 2:30 PM
WDoranWMF moved this task from Backlog to Next Sprint on the Platform Team Workboards (Green) board.
WDoranWMF updated the task description. (Show Details)Oct 16 2019, 7:36 PM
WDoranWMF set the point value for this task to 1.Oct 16 2019, 7:38 PM
BPirkle claimed this task.Oct 23 2019, 3:11 PM
BPirkle moved this task from Ready to Doing on the Platform Team Workboards (Green) board.
SELECT  rev_id,rev_page,rev_timestamp,rev_minor_edit,rev_deleted,rev_len,rev_parent_id,rev_sha1,
  comment_rev_comment.comment_text AS `rev_comment_text`,
  comment_rev_comment.comment_data AS `rev_comment_data`,
  comment_rev_comment.comment_id AS `rev_comment_cid`,
  actor_rev_user.actor_user AS `rev_user`,
  actor_rev_user.actor_name AS `rev_user_text`,
  temp_rev_user.revactor_actor AS `rev_actor`
FROM `revision`
JOIN `revision_comment_temp` `temp_rev_comment`
  ON ((temp_rev_comment.revcomment_rev = rev_id))
JOIN `comment` `comment_rev_comment`
  ON ((comment_rev_comment.comment_id = temp_rev_comment.revcomment_comment_id))
JOIN `revision_actor_temp` `temp_rev_user`
  ON ((temp_rev_user.revactor_rev = rev_id AND revactor_page = rev_page))
JOIN `actor` `actor_rev_user`
  ON ((actor_rev_user.actor_id = temp_rev_user.revactor_actor))
WHERE rev_page = ?
  AND (rev_minor_edit != 0)
ORDER BY rev_timestamp DESC, rev_id DESC
LIMIT 21;

The only difference between this query and the base query (without filters) is the "AND (rev_minor_edit != 0)" in the WHERE

For rev_page 534366 (Barack_Obama):

21 rows in set (0.00 sec)

EXPLAIN gives:

+------+-------------+---------------------+--------+-----------------------------------------------------------+----------------+---------+-----------------------------------------------+-------+-------------+
| id   | select_type | table               | type   | possible_keys                                             | key            | key_len | ref                                           | rows  | Extra       |
+------+-------------+---------------------+--------+-----------------------------------------------------------+----------------+---------+-----------------------------------------------+-------+-------------+
|    1 | SIMPLE      | revision            | ref    | PRIMARY,page_timestamp,page_user_timestamp,rev_page_id    | page_timestamp | 4       | const                                         | 50390 | Using where |
|    1 | SIMPLE      | temp_rev_comment    | ref    | PRIMARY,revcomment_rev                                    | PRIMARY        | 4       | enwiki.revision.rev_id                        |     1 | Using index |
|    1 | SIMPLE      | comment_rev_comment | eq_ref | PRIMARY                                                   | PRIMARY        | 8       | enwiki.temp_rev_comment.revcomment_comment_id |     1 |             |
|    1 | SIMPLE      | temp_rev_user       | eq_ref | PRIMARY,revactor_rev,actor_timestamp,page_actor_timestamp | revactor_rev   | 4       | enwiki.revision.rev_id                        |     1 | Using where |
|    1 | SIMPLE      | actor_rev_user      | eq_ref | PRIMARY                                                   | PRIMARY        | 8       | enwiki.temp_rev_user.revactor_actor           |     1 |             |
+------+-------------+---------------------+--------+-----------------------------------------------------------+----------------+---------+-----------------------------------------------+-------+-------------+

Per IRC discussion, this query is approved. The query in T235572: Compose query for minor edit count is still being discussed, but does not block this task, or other engineering tasks for the related user story.

eprodromou closed this task as Resolved.Oct 29 2019, 4:36 PM