Page MenuHomePhabricator

abstract dump queries with and without order by page id

Authored By
ArielGlenn
May 24 2019, 6:26 PM
Size
13 KB
Subscribers
None

abstract dump queries with and without order by page id

*** SECTION: s1
*** HOST: db1106.eqiad.wmnet
*** WIKI: enwiki
*** QUERY:
#
# Query for abstract dumps without offset
# uses: dumpPages ( $this->history & self::CURRENT )
# order by page id
#
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`, page_namespace,page_title,page_id,page_latest,page_is_redirect,page_len,page_restrictions, 1 AS `_load_content` FROM `page` JOIN `revision` ON ((page_id=rev_page AND page_latest=rev_id)) 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)) JOIN `actor` `actor_rev_user` ON ((actor_rev_user.actor_id = temp_rev_user.revactor_actor)) WHERE (page_id >= 6500000 AND page_id < 6600000) AND (rev_page>0 OR (rev_page=0 AND rev_id>0)) ORDER BY page_id ASC LIMIT 50000
*** SHOW EXPLAIN RESULTS:
+----+-------------+---------------------+--------+--------------------------------------------------------+---------+---------+-----------------------------------------------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------+--------+--------------------------------------------------------+---------+---------+-----------------------------------------------+--------+-----------------------------+
| 1 | SIMPLE | page | range | PRIMARY | PRIMARY | 4 | None | 143958 | Using where; Using filesort |
| 1 | SIMPLE | revision | eq_ref | PRIMARY,rev_page_id,page_timestamp,page_user_timestamp | PRIMARY | 4 | enwiki.page.page_latest | 1 | Using where |
| 1 | SIMPLE | temp_rev_comment | ref | PRIMARY,revcomment_rev | PRIMARY | 4 | enwiki.page.page_latest | 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 | ref | PRIMARY,revactor_rev,actor_timestamp | PRIMARY | 4 | enwiki.page.page_latest | 1 | Using index |
| 1 | SIMPLE | actor_rev_user | eq_ref | PRIMARY | PRIMARY | 8 | enwiki.temp_rev_user.revactor_actor | 1 | |
+----+-------------+---------------------+--------+--------------------------------------------------------+---------+---------+-----------------------------------------------+--------+-----------------------------+
*** QUERY:
#
# Query for abstract dumps with offset
# uses: dumpPages ( $this->history & self::CURRENT )
# order by page id
#
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`, page_namespace,page_title,page_id,page_latest,page_is_redirect,page_len,page_restrictions, 1 AS `_load_content` FROM `page` JOIN `revision` ON ((page_id=rev_page AND page_latest=rev_id)) 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)) JOIN `actor` `actor_rev_user` ON ((actor_rev_user.actor_id = temp_rev_user.revactor_actor)) WHERE (page_id >= 6500000 AND page_id < 6600000) AND (rev_page>6572619 OR (rev_page=6572619 AND rev_id>342490091)) ORDER BY page_id ASC LIMIT 50000
*** SHOW EXPLAIN RESULTS:
+----+-------------+---------------------+--------+--------------------------------------------------------+---------+---------+-----------------------------------------------+-------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------+--------+--------------------------------------------------------+---------+---------+-----------------------------------------------+-------+-----------------------------+
| 1 | SIMPLE | page | range | PRIMARY | PRIMARY | 4 | None | 37722 | Using where; Using filesort |
| 1 | SIMPLE | revision | eq_ref | PRIMARY,rev_page_id,page_timestamp,page_user_timestamp | PRIMARY | 4 | enwiki.page.page_latest | 1 | Using where |
| 1 | SIMPLE | temp_rev_comment | ref | PRIMARY,revcomment_rev | PRIMARY | 4 | enwiki.page.page_latest | 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 | ref | PRIMARY,revactor_rev,actor_timestamp | PRIMARY | 4 | enwiki.page.page_latest | 1 | Using index |
| 1 | SIMPLE | actor_rev_user | eq_ref | PRIMARY | PRIMARY | 8 | enwiki.temp_rev_user.revactor_actor | 1 | |
+----+-------------+---------------------+--------+--------------------------------------------------------+---------+---------+-----------------------------------------------+-------+-----------------------------+
*** QUERY:
#
# Query for abstract dumps without offset
# uses: dumpPages ( $this->history & self::CURRENT )
# order by rev_page
#
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`, page_namespace,page_title,page_id,page_latest,page_is_redirect,page_len,page_restrictions, 1 AS `_load_content` FROM `page` JOIN `revision` ON ((page_id=rev_page AND page_latest=rev_id)) 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)) JOIN `actor` `actor_rev_user` ON ((actor_rev_user.actor_id = temp_rev_user.revactor_actor)) WHERE (page_id >= 6500000 AND page_id < 6600000) AND (rev_page>0 OR (rev_page=0 AND rev_id>0)) ORDER BY rev_page ASC,rev_id ASC LIMIT 50000
*** SHOW EXPLAIN RESULTS:
+----+-------------+---------------------+--------+--------------------------------------------------------+---------+---------+-----------------------------------------------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------+--------+--------------------------------------------------------+---------+---------+-----------------------------------------------+--------+----------------------------------------------+
| 1 | SIMPLE | page | range | PRIMARY | PRIMARY | 4 | None | 143958 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | revision | eq_ref | PRIMARY,rev_page_id,page_timestamp,page_user_timestamp | PRIMARY | 4 | enwiki.page.page_latest | 1 | Using where |
| 1 | SIMPLE | temp_rev_comment | ref | PRIMARY,revcomment_rev | PRIMARY | 4 | enwiki.page.page_latest | 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 | ref | PRIMARY,revactor_rev,actor_timestamp | PRIMARY | 4 | enwiki.page.page_latest | 1 | Using index |
| 1 | SIMPLE | actor_rev_user | eq_ref | PRIMARY | PRIMARY | 8 | enwiki.temp_rev_user.revactor_actor | 1 | |
+----+-------------+---------------------+--------+--------------------------------------------------------+---------+---------+-----------------------------------------------+--------+----------------------------------------------+
*** QUERY:
#
# Query for abstract dumps with offset
# uses: dumpPages ( $this->history & self::CURRENT )
# order by rev_page
#
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`, page_namespace,page_title,page_id,page_latest,page_is_redirect,page_len,page_restrictions, 1 AS `_load_content` FROM `page` JOIN `revision` ON ((page_id=rev_page AND page_latest=rev_id)) 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)) JOIN `actor` `actor_rev_user` ON ((actor_rev_user.actor_id = temp_rev_user.revactor_actor)) WHERE (page_id >= 6500000 AND page_id < 6600000) AND (rev_page>6572619 OR (rev_page=6572619 AND rev_id>342490091)) ORDER BY rev_page ASC,rev_id ASC LIMIT 50000
*** SHOW EXPLAIN RESULTS:
+----+-------------+---------------------+--------+--------------------------------------------------------+---------+---------+-----------------------------------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------+--------+--------------------------------------------------------+---------+---------+-----------------------------------------------+-------+----------------------------------------------+
| 1 | SIMPLE | page | range | PRIMARY | PRIMARY | 4 | None | 37722 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | revision | eq_ref | PRIMARY,rev_page_id,page_timestamp,page_user_timestamp | PRIMARY | 4 | enwiki.page.page_latest | 1 | Using where |
| 1 | SIMPLE | temp_rev_comment | ref | PRIMARY,revcomment_rev | PRIMARY | 4 | enwiki.page.page_latest | 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 | ref | PRIMARY,revactor_rev,actor_timestamp | PRIMARY | 4 | enwiki.page.page_latest | 1 | Using index |
| 1 | SIMPLE | actor_rev_user | eq_ref | PRIMARY | PRIMARY | 8 | enwiki.temp_rev_user.revactor_actor | 1 | |
+----+-------------+---------------------+--------+--------------------------------------------------------+---------+---------+-----------------------------------------------+-------+----------------------------------------------+

File Metadata

Mime Type
text/x-diff
Storage Engine
blob
Storage Format
Raw Data
Storage Handle
7519987

Event Timeline