Page MenuHomePhabricator
Paste P8571

eowiki: order by rev_page or page_id, doing abstracts dump
ActivePublic

Authored by ArielGlenn on May 29 2019, 12:51 PM.
ariel@mwmaint1002:~/table_checker$ python3 ./explain_sql_query.py --yamlfile explain-queries.yaml.eowiki --queryfile ./queries_to_explain_bigbatch.sql --settings ./eqiad.conf > abstract_query_results_eowiki.txt
ariel@mwmaint1002:~/table_checker$ more abstract_query_results_eowiki.txt
*** SECTION: s2
*** HOST: db1090.eqiad.wmnet:3312
*** WIKI: eowiki
*** 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_d
ata`, 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_namespa
ce,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_acto
r_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 >= 500000 AND page_id < 64000
0) AND (rev_page>0 OR (rev_page=0 AND rev_id>0)) ORDER BY page_id ASC LIMIT 150000
*** SHOW EXPLAIN RESULTS:
+----+-------------+---------------------+--------+--------------------------------------------------------+---------+---------+-----------------------------------------------+--------+------------------
-----------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
|
+----+-------------+---------------------+--------+--------------------------------------------------------+---------+---------+-----------------------------------------------+--------+------------------
-----------+
| 1 | SIMPLE | page | range | PRIMARY | PRIMARY | 4 | None | 254074 | Using where; Usin
g filesort |
| 1 | SIMPLE | revision | eq_ref | PRIMARY,page_timestamp,page_user_timestamp,rev_page_id | PRIMARY | 4 | eowiki.page.page_latest | 1 | Using where
|
| 1 | SIMPLE | temp_rev_user | ref | PRIMARY,revactor_rev,actor_timestamp | PRIMARY | 4 | eowiki.page.page_latest | 1 | Using index
|
| 1 | SIMPLE | actor_rev_user | eq_ref | PRIMARY | PRIMARY | 8 | eowiki.temp_rev_user.revactor_actor | 1 |
|
| 1 | SIMPLE | temp_rev_comment | ref | PRIMARY,revcomment_rev | PRIMARY | 4 | eowiki.page.page_latest | 1 | Using index
|
| 1 | SIMPLE | comment_rev_comment | eq_ref | PRIMARY | PRIMARY | 8 | eowiki.temp_rev_comment.revcomment_comment_id | 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_d
ata`, 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_namespa
ce,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_acto
r_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 >= 500000 AND page_id < 64000
0) AND (rev_page>520500 OR (rev_page=520500 AND rev_id>5646197)) ORDER BY page_id ASC LIMIT 150000
*** SHOW EXPLAIN RESULTS:
no results available
*** 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_d
ata`, 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_namespa
ce,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_acto
r_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 >= 500000 AND page_id < 64000
0) AND (rev_page>0 OR (rev_page=0 AND rev_id>0)) ORDER BY rev_page ASC,rev_id ASC LIMIT 150000
*** SHOW EXPLAIN RESULTS:
+----+-------------+---------------------+--------+--------------------------------------------------------+---------+---------+-----------------------------------------------+--------+------------------
----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
|
+----+-------------+---------------------+--------+--------------------------------------------------------+---------+---------+-----------------------------------------------+--------+------------------
----------------------------+
| 1 | SIMPLE | page | range | PRIMARY | PRIMARY | 4 | None | 254074 | Using where; Usin
g temporary; Using filesort |
| 1 | SIMPLE | revision | eq_ref | PRIMARY,page_timestamp,page_user_timestamp,rev_page_id | PRIMARY | 4 | eowiki.page.page_latest | 1 | Using where
|
| 1 | SIMPLE | temp_rev_user | ref | PRIMARY,revactor_rev,actor_timestamp | PRIMARY | 4 | eowiki.page.page_latest | 1 | Using index
|
| 1 | SIMPLE | actor_rev_user | eq_ref | PRIMARY | PRIMARY | 8 | eowiki.temp_rev_user.revactor_actor | 1 |
|
| 1 | SIMPLE | temp_rev_comment | ref | PRIMARY,revcomment_rev | PRIMARY | 4 | eowiki.page.page_latest | 1 | Using index
|
| 1 | SIMPLE | comment_rev_comment | eq_ref | PRIMARY | PRIMARY | 8 | eowiki.temp_rev_comment.revcomment_comment_id | 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_d
ata`, 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_namespa
ce,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_acto
r_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 >= 500000 AND page_id < 64000
0) AND (rev_page>520500 OR (rev_page=520500 AND rev_id>5646197)) ORDER BY rev_page ASC,rev_id ASC LIMIT 150000
*** SHOW EXPLAIN RESULTS:
+----+-------------+---------------------+--------+--------------------------------------------------------+---------+---------+-----------------------------------------------+--------+------------------
----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
|
+----+-------------+---------------------+--------+--------------------------------------------------------+---------+---------+-----------------------------------------------+--------+------------------
----------------------------+
| 1 | SIMPLE | page | range | PRIMARY | PRIMARY | 4 | None | 231236 | Using where; Usin
g temporary; Using filesort |
| 1 | SIMPLE | revision | eq_ref | PRIMARY,page_timestamp,page_user_timestamp,rev_page_id | PRIMARY | 4 | eowiki.page.page_latest | 1 | Using where
|
| 1 | SIMPLE | temp_rev_user | ref | PRIMARY,revactor_rev,actor_timestamp | PRIMARY | 4 | eowiki.page.page_latest | 1 | Using index
|
| 1 | SIMPLE | actor_rev_user | eq_ref | PRIMARY | PRIMARY | 8 | eowiki.temp_rev_user.revactor_actor | 1 |
|
| 1 | SIMPLE | temp_rev_comment | ref | PRIMARY,revcomment_rev | PRIMARY | 4 | eowiki.page.page_latest | 1 | Using index
|
| 1 | SIMPLE | comment_rev_comment | eq_ref | PRIMARY | PRIMARY | 8 | eowiki.temp_rev_comment.revcomment_comment_id | 1 |
|
+----+-------------+---------------------+--------+--------------------------------------------------------+---------+---------+-----------------------------------------------+--------+------------------
----------------------------+
ariel@mwmaint1002:~/table_checker$