{"action":"created","comment":{"url":"https://api.github.com/repos/pingcap/docs/pulls/comments/216888364","pull_request_review_id":154082152,"id":216888364,"node_id":"MDI0OlB1bGxSZXF1ZXN0UmV2aWV3Q29tbWVudDIxNjg4ODM2NA==","diff_hunk":"@@ -29,6 +29,45 @@ Currently, the `EXPLAIN` statement returns the following four columns: id, count\n | task | the task that the current operator belongs to. The current execution plan contains two types of tasks: 1) the **root** task that runs on the TiDB server; 2) the **cop** task that runs concurrently on the TiKV server. The topological relations of the current execution plan in the task level is that a root task can be followed by many cop tasks. The root task uses the output of cop task as the input. The cop task executes the tasks that TiDB pushes to TiKV. Each cop task scatters in the TiKV cluster and is executed by multiple processes. |\n | operator info | The details about each operator. The information of each operator differs from others, see [Operator Info](#operator-info).|\n \n+### Example Usage\n+\n+Using the [bikeshare example database](../bikeshare-example-database.md):\n+\n+```\n+mysql> EXPLAIN SELECT count(*) FROM trips WHERE start_date BETWEEN '2017-07-01 00:00:00' AND '2017-07-01 23:59:59';\n++--------------------------+-------------+------+------------------------------------------------------------------------------------------------------------------------+\n+| id                       | count       | task | operator info                                                                                                          |\n++--------------------------+-------------+------+------------------------------------------------------------------------------------------------------------------------+\n+| StreamAgg_20             | 1.00        | root | funcs:count(col_0)                                                                                                     |\n+| └─TableReader_21         | 1.00        | root | data:StreamAgg_9                                                                                                       |\n+|   └─StreamAgg_9          | 1.00        | cop  | funcs:count(1)                                                                                                         |\n+|     └─Selection_19       | 8166.73     | cop  | ge(bikeshare.trips.start_date, 2017-07-01 00:00:00.000000), le(bikeshare.trips.start_date, 2017-07-01 23:59:59.000000) |\n+|       └─TableScan_18     | 19117643.00 | cop  | table:trips, range:[-inf,+inf], keep order:false                                                                       |\n++--------------------------+-------------+------+------------------------------------------------------------------------------------------------------------------------+\n+5 rows in set (0.00 sec)\n+```\n+\n+Here we can see that the coprocesor (cop) needs to scan the table `trips` to find rows that match the criteria of `start_date`. Rows that meet this criteria are determined in `Selection_19` and passed to `StreamAgg_9`, all still within the coprocessor (i.e. inside of TiKV). The `count` column shows an approximate number of rows that will be processed, which is estimated with the help of table statistics. In this query it is estimated that each of the TiKV nodes will return `1.00` row to TiDB (as `TableReader_21`), which are then aggregated as `StreamAgg_20` to return an estimated `1.00` row to the client.\n+\n+The good news with this query is that most of the work is pushed down to the coprocessor. This means that minimal data transfer way required for query execution. However, the `TableScan_18` can be eliminated by adding an index to speed up queries on `start_date`:\n+\n+```\n+mysql> ALTER TABLE trips ADD INDEX (start_date);\n+..\n+mysql> EXPLAIN SELECT count(*) FROM trips WHERE start_date BETWEEN '2017-07-01 00:00:00' AND '2017-07-01 23:59:59';\n++------------------------+---------+------+--------------------------------------------------------------------------------------------------+\n+| id                     | count   | task | operator info                                                                                    |\n++------------------------+---------+------+--------------------------------------------------------------------------------------------------+\n+| StreamAgg_25           | 1.00    | root | funcs:count(col_0)                                                                               |\n+| └─IndexReader_26       | 1.00    | root | index:StreamAgg_9                                                                                |\n+|   └─StreamAgg_9        | 1.00    | cop  | funcs:count(1)                                                                                   |\n+|     └─IndexScan_24     | 8166.73 | cop  | table:trips, index:start_date, range:[2017-07-01 00:00:00,2017-07-01 23:59:59], keep order:false |\n++------------------------+---------+------+--------------------------------------------------------------------------------------------------+\n+4 rows in set (0.01 sec)\n+```\n+\n+In the revisted `EXPLAIN` we can see the count of rows scanned has reduced via the use of an index. On a reference system, this reduced query execution time reduced from 50.41 seconds to 0.00 seconds!","path":"sql/understanding-the-query-execution-plan.md","position":41,"original_position":41,"commit_id":"422f4df7dbdd2798fc69b6dd673ab56c4361142c","original_commit_id":"422f4df7dbdd2798fc69b6dd673ab56c4361142c","user":{"login":"lilin90","id":30922556,"node_id":"MDQ6VXNlcjMwOTIyNTU2","avatar_url":"https://avatars1.githubusercontent.com/u/30922556?v=4","gravatar_id":"","url":"https://api.github.com/users/lilin90","html_url":"https://github.com/lilin90","followers_url":"https://api.github.com/users/lilin90/followers","following_url":"https://api.github.com/users/lilin90/following{/other_user}","gists_url":"https://api.github.com/users/lilin90/gists{/gist_id}","starred_url":"https://api.github.com/users/lilin90/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/lilin90/subscriptions","organizations_url":"https://api.github.com/users/lilin90/orgs","repos_url":"https://api.github.com/users/lilin90/repos","events_url":"https://api.github.com/users/lilin90/events{/privacy}","received_events_url":"https://api.github.com/users/lilin90/received_events","type":"User","site_admin":false},"body":"- revisted -> revisited\r\n- we -> you\r\n- this reduced query execution time -> the query execution time (since there is another \"reduced\" in this sentence)","created_at":"2018-09-12T03:39:54Z","updated_at":"2018-09-12T03:40:00Z","html_url":"https://github.com/pingcap/docs/pull/599#discussion_r216888364","pull_request_url":"https://api.github.com/repos/pingcap/docs/pulls/599","author_association":"CONTRIBUTOR","_links":{"self":{"href":"https://api.github.com/repos/pingcap/docs/pulls/comments/216888364"},"html":{"href":"https://github.com/pingcap/docs/pull/599#discussion_r216888364"},"pull_request":{"href":"https://api.github.com/repos/pingcap/docs/pulls/599"}}},"pull_request":{"url":"https://api.github.com/repos/pingcap/docs/pulls/599","id":213420072,"node_id":"MDExOlB1bGxSZXF1ZXN0MjEzNDIwMDcy","html_url":"https://github.com/pingcap/docs/pull/599","diff_url":"https://github.com/pingcap/docs/pull/599.diff","patch_url":"https://github.com/pingcap/docs/pull/599.patch","issue_url":"https://api.github.com/repos/pingcap/docs/issues/599","number":599,"state":"open","locked":false,"title":"sql: Add example to QEP manual page","user":{"login":"morgo","id":57982,"node_id":"MDQ6VXNlcjU3OTgy","avatar_url":"https://avatars2.githubusercontent.com/u/57982?v=4","gravatar_id":"","url":"https://api.github.com/users/morgo","html_url":"https://github.com/morgo","followers_url":"https://api.github.com/users/morgo/followers","following_url":"https://api.github.com/users/morgo/following{/other_user}","gists_url":"https://api.github.com/users/morgo/gists{/gist_id}","starred_url":"https://api.github.com/users/morgo/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/morgo/subscriptions","organizations_url":"https://api.github.com/users/morgo/orgs","repos_url":"https://api.github.com/users/morgo/repos","events_url":"https://api.github.com/users/morgo/events{/privacy}","received_events_url":"https://api.github.com/users/morgo/received_events","type":"User","site_admin":false},"body":"This adds an example to the Query Execution Plan page.\r\n\r\nIt depends on https://github.com/pingcap/docs/pull/596 which is also ready to merge.","created_at":"2018-09-05T20:36:46Z","updated_at":"2018-09-12T03:40:00Z","closed_at":null,"merged_at":null,"merge_commit_sha":"cb4183821e905e0524de9af0f0151d599b6afe75","assignee":null,"assignees":[],"requested_reviewers":[],"requested_teams":[],"labels":[{"id":412809762,"node_id":"MDU6TGFiZWw0MTI4MDk3NjI=","url":"https://api.github.com/repos/pingcap/docs/labels/enhancement","name":"enhancement","color":"84b6eb","default":true}],"milestone":null,"commits_url":"https://api.github.com/repos/pingcap/docs/pulls/599/commits","review_comments_url":"https://api.github.com/repos/pingcap/docs/pulls/599/comments","review_comment_url":"https://api.github.com/repos/pingcap/docs/pulls/comments{/number}","comments_url":"https://api.github.com/repos/pingcap/docs/issues/599/comments","statuses_url":"https://api.github.com/repos/pingcap/docs/statuses/422f4df7dbdd2798fc69b6dd673ab56c4361142c","head":{"label":"morgo:patch-6","ref":"patch-6","sha":"422f4df7dbdd2798fc69b6dd673ab56c4361142c","user":{"login":"morgo","id":57982,"node_id":"MDQ6VXNlcjU3OTgy","avatar_url":"https://avatars2.githubusercontent.com/u/57982?v=4","gravatar_id":"","url":"https://api.github.com/users/morgo","html_url":"https://github.com/morgo","followers_url":"https://api.github.com/users/morgo/followers","following_url":"https://api.github.com/users/morgo/following{/other_user}","gists_url":"https://api.github.com/users/morgo/gists{/gist_id}","starred_url":"https://api.github.com/users/morgo/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/morgo/subscriptions","organizations_url":"https://api.github.com/users/morgo/orgs","repos_url":"https://api.github.com/users/morgo/repos","events_url":"https://api.github.com/users/morgo/events{/privacy}","received_events_url":"https://api.github.com/users/morgo/received_events","type":"User","site_admin":false},"repo":{"id":140019100,"node_id":"MDEwOlJlcG9zaXRvcnkxNDAwMTkxMDA=","name":"docs","full_name":"morgo/docs","owner":{"login":"morgo","id":57982,"node_id":"MDQ6VXNlcjU3OTgy","avatar_url":"https://avatars2.githubusercontent.com/u/57982?v=4","gravatar_id":"","url":"https://api.github.com/users/morgo","html_url":"https://github.com/morgo","followers_url":"https://api.github.com/users/morgo/followers","following_url":"https://api.github.com/users/morgo/following{/other_user}","gists_url":"https://api.github.com/users/morgo/gists{/gist_id}","starred_url":"https://api.github.com/users/morgo/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/morgo/subscriptions","organizations_url":"https://api.github.com/users/morgo/orgs","repos_url":"https://api.github.com/users/morgo/repos","events_url":"https://api.github.com/users/morgo/events{/privacy}","received_events_url":"https://api.github.com/users/morgo/received_events","type":"User","site_admin":false},"private":false,"html_url":"https://github.com/morgo/docs","description":"TiDB/TiKV/PD documents.","fork":true,"url":"https://api.github.com/repos/morgo/docs","forks_url":"https://api.github.com/repos/morgo/docs/forks","keys_url":"https://api.github.com/repos/morgo/docs/keys{/key_id}","collaborators_url":"https://api.github.com/repos/morgo/docs/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/morgo/docs/teams","hooks_url":"https://api.github.com/repos/morgo/docs/hooks","issue_events_url":"https://api.github.com/repos/morgo/docs/issues/events{/number}","events_url":"https://api.github.com/repos/morgo/docs/events","assignees_url":"https://api.github.com/repos/morgo/docs/assignees{/user}","branches_url":"https://api.github.com/repos/morgo/docs/branches{/branch}","tags_url":"https://api.github.com/repos/morgo/docs/tags","blobs_url":"https://api.github.com/repos/morgo/docs/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/morgo/docs/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/morgo/docs/git/refs{/sha}","trees_url":"https://api.github.com/repos/morgo/docs/git/trees{/sha}","statuses_url":"https://api.github.com/repos/morgo/docs/statuses/{sha}","languages_url":"https://api.github.com/repos/morgo/docs/languages","stargazers_url":"https://api.github.com/repos/morgo/docs/stargazers","contributors_url":"https://api.github.com/repos/morgo/docs/contributors","subscribers_url":"https://api.github.com/repos/morgo/docs/subscribers","subscription_url":"https://api.github.com/repos/morgo/docs/subscription","commits_url":"https://api.github.com/repos/morgo/docs/commits{/sha}","git_commits_url":"https://api.github.com/repos/morgo/docs/git/commits{/sha}","comments_url":"https://api.github.com/repos/morgo/docs/comments{/number}","issue_comment_url":"https://api.github.com/repos/morgo/docs/issues/comments{/number}","contents_url":"https://api.github.com/repos/morgo/docs/contents/{+path}","compare_url":"https://api.github.com/repos/morgo/docs/compare/{base}...{head}","merges_url":"https://api.github.com/repos/morgo/docs/merges","archive_url":"https://api.github.com/repos/morgo/docs/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/morgo/docs/downloads","issues_url":"https://api.github.com/repos/morgo/docs/issues{/number}","pulls_url":"https://api.github.com/repos/morgo/docs/pulls{/number}","milestones_url":"https://api.github.com/repos/morgo/docs/milestones{/number}","notifications_url":"https://api.github.com/repos/morgo/docs/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/morgo/docs/labels{/name}","releases_url":"https://api.github.com/repos/morgo/docs/releases{/id}","deployments_url":"https://api.github.com/repos/morgo/docs/deployments","created_at":"2018-07-06T18:40:05Z","updated_at":"2018-09-08T20:36:34Z","pushed_at":"2018-09-12T03:32:00Z","git_url":"git://github.com/morgo/docs.git","ssh_url":"git@github.com:morgo/docs.git","clone_url":"https://github.com/morgo/docs.git","svn_url":"https://github.com/morgo/docs","homepage":"https://pingcap.github.io/docs","size":7278,"stargazers_count":0,"watchers_count":0,"language":"HTML","has_issues":false,"has_projects":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":0,"mirror_url":null,"archived":false,"open_issues_count":0,"license":{"key":"apache-2.0","name":"Apache License 2.0","spdx_id":"Apache-2.0","url":"https://api.github.com/licenses/apache-2.0","node_id":"MDc6TGljZW5zZTI="},"forks":0,"open_issues":0,"watchers":0,"default_branch":"master"}},"base":{"label":"pingcap:master","ref":"master","sha":"c87fa2962f5b5b75e3d01b8f7ddaa07373c87151","user":{"login":"pingcap","id":11855343,"node_id":"MDEyOk9yZ2FuaXphdGlvbjExODU1MzQz","avatar_url":"https://avatars3.githubusercontent.com/u/11855343?v=4","gravatar_id":"","url":"https://api.github.com/users/pingcap","html_url":"https://github.com/pingcap","followers_url":"https://api.github.com/users/pingcap/followers","following_url":"https://api.github.com/users/pingcap/following{/other_user}","gists_url":"https://api.github.com/users/pingcap/gists{/gist_id}","starred_url":"https://api.github.com/users/pingcap/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/pingcap/subscriptions","organizations_url":"https://api.github.com/users/pingcap/orgs","repos_url":"https://api.github.com/users/pingcap/repos","events_url":"https://api.github.com/users/pingcap/events{/privacy}","received_events_url":"https://api.github.com/users/pingcap/received_events","type":"Organization","site_admin":false},"repo":{"id":63995402,"node_id":"MDEwOlJlcG9zaXRvcnk2Mzk5NTQwMg==","name":"docs","full_name":"pingcap/docs","owner":{"login":"pingcap","id":11855343,"node_id":"MDEyOk9yZ2FuaXphdGlvbjExODU1MzQz","avatar_url":"https://avatars3.githubusercontent.com/u/11855343?v=4","gravatar_id":"","url":"https://api.github.com/users/pingcap","html_url":"https://github.com/pingcap","followers_url":"https://api.github.com/users/pingcap/followers","following_url":"https://api.github.com/users/pingcap/following{/other_user}","gists_url":"https://api.github.com/users/pingcap/gists{/gist_id}","starred_url":"https://api.github.com/users/pingcap/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/pingcap/subscriptions","organizations_url":"https://api.github.com/users/pingcap/orgs","repos_url":"https://api.github.com/users/pingcap/repos","events_url":"https://api.github.com/users/pingcap/events{/privacy}","received_events_url":"https://api.github.com/users/pingcap/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/pingcap/docs","description":"TiDB/TiKV/PD documents.","fork":false,"url":"https://api.github.com/repos/pingcap/docs","forks_url":"https://api.github.com/repos/pingcap/docs/forks","keys_url":"https://api.github.com/repos/pingcap/docs/keys{/key_id}","collaborators_url":"https://api.github.com/repos/pingcap/docs/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/pingcap/docs/teams","hooks_url":"https://api.github.com/repos/pingcap/docs/hooks","issue_events_url":"https://api.github.com/repos/pingcap/docs/issues/events{/number}","events_url":"https://api.github.com/repos/pingcap/docs/events","assignees_url":"https://api.github.com/repos/pingcap/docs/assignees{/user}","branches_url":"https://api.github.com/repos/pingcap/docs/branches{/branch}","tags_url":"https://api.github.com/repos/pingcap/docs/tags","blobs_url":"https://api.github.com/repos/pingcap/docs/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/pingcap/docs/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/pingcap/docs/git/refs{/sha}","trees_url":"https://api.github.com/repos/pingcap/docs/git/trees{/sha}","statuses_url":"https://api.github.com/repos/pingcap/docs/statuses/{sha}","languages_url":"https://api.github.com/repos/pingcap/docs/languages","stargazers_url":"https://api.github.com/repos/pingcap/docs/stargazers","contributors_url":"https://api.github.com/repos/pingcap/docs/contributors","subscribers_url":"https://api.github.com/repos/pingcap/docs/subscribers","subscription_url":"https://api.github.com/repos/pingcap/docs/subscription","commits_url":"https://api.github.com/repos/pingcap/docs/commits{/sha}","git_commits_url":"https://api.github.com/repos/pingcap/docs/git/commits{/sha}","comments_url":"https://api.github.com/repos/pingcap/docs/comments{/number}","issue_comment_url":"https://api.github.com/repos/pingcap/docs/issues/comments{/number}","contents_url":"https://api.github.com/repos/pingcap/docs/contents/{+path}","compare_url":"https://api.github.com/repos/pingcap/docs/compare/{base}...{head}","merges_url":"https://api.github.com/repos/pingcap/docs/merges","archive_url":"https://api.github.com/repos/pingcap/docs/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/pingcap/docs/downloads","issues_url":"https://api.github.com/repos/pingcap/docs/issues{/number}","pulls_url":"https://api.github.com/repos/pingcap/docs/pulls{/number}","milestones_url":"https://api.github.com/repos/pingcap/docs/milestones{/number}","notifications_url":"https://api.github.com/repos/pingcap/docs/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/pingcap/docs/labels{/name}","releases_url":"https://api.github.com/repos/pingcap/docs/releases{/id}","deployments_url":"https://api.github.com/repos/pingcap/docs/deployments","created_at":"2016-07-23T02:39:04Z","updated_at":"2018-09-12T03:31:56Z","pushed_at":"2018-09-12T03:31:54Z","git_url":"git://github.com/pingcap/docs.git","ssh_url":"git@github.com:pingcap/docs.git","clone_url":"https://github.com/pingcap/docs.git","svn_url":"https://github.com/pingcap/docs","homepage":"https://pingcap.github.io/docs","size":7212,"stargazers_count":194,"watchers_count":194,"language":"HTML","has_issues":true,"has_projects":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":132,"mirror_url":null,"archived":false,"open_issues_count":8,"license":{"key":"apache-2.0","name":"Apache License 2.0","spdx_id":"Apache-2.0","url":"https://api.github.com/licenses/apache-2.0","node_id":"MDc6TGljZW5zZTI="},"forks":132,"open_issues":8,"watchers":194,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/pingcap/docs/pulls/599"},"html":{"href":"https://github.com/pingcap/docs/pull/599"},"issue":{"href":"https://api.github.com/repos/pingcap/docs/issues/599"},"comments":{"href":"https://api.github.com/repos/pingcap/docs/issues/599/comments"},"review_comments":{"href":"https://api.github.com/repos/pingcap/docs/pulls/599/comments"},"review_comment":{"href":"https://api.github.com/repos/pingcap/docs/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/pingcap/docs/pulls/599/commits"},"statuses":{"href":"https://api.github.com/repos/pingcap/docs/statuses/422f4df7dbdd2798fc69b6dd673ab56c4361142c"}},"author_association":"MEMBER"}}
{"actor":{"display_login":"lilin90"}}