{"action":"created","issue":{"url":"https://api.github.com/repos/pingcap/tidb/issues/4188","repository_url":"https://api.github.com/repos/pingcap/tidb","labels_url":"https://api.github.com/repos/pingcap/tidb/issues/4188/labels{/name}","comments_url":"https://api.github.com/repos/pingcap/tidb/issues/4188/comments","events_url":"https://api.github.com/repos/pingcap/tidb/issues/4188/events","html_url":"https://github.com/pingcap/tidb/issues/4188","id":250277558,"number":4188,"title":"TiDB cost base optimizer not working well","user":{"login":"blacktear23","id":209576,"avatar_url":"https://avatars2.githubusercontent.com/u/209576?v=4","gravatar_id":"","url":"https://api.github.com/users/blacktear23","html_url":"https://github.com/blacktear23","followers_url":"https://api.github.com/users/blacktear23/followers","following_url":"https://api.github.com/users/blacktear23/following{/other_user}","gists_url":"https://api.github.com/users/blacktear23/gists{/gist_id}","starred_url":"https://api.github.com/users/blacktear23/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/blacktear23/subscriptions","organizations_url":"https://api.github.com/users/blacktear23/orgs","repos_url":"https://api.github.com/users/blacktear23/repos","events_url":"https://api.github.com/users/blacktear23/events{/privacy}","received_events_url":"https://api.github.com/users/blacktear23/received_events","type":"User","site_admin":false},"labels":[{"id":256872088,"url":"https://api.github.com/repos/pingcap/tidb/labels/enhancement","name":"enhancement","color":"84b6eb","default":true},{"id":667671533,"url":"https://api.github.com/repos/pingcap/tidb/labels/optimizer","name":"optimizer","color":"1d76db","default":false}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":1,"created_at":"2017-08-15T10:25:31Z","updated_at":"2017-08-18T01:22:01Z","closed_at":null,"body":"Please answer these questions before submitting your issue. Thanks!\r\n\r\n1. What did you do?\r\nUse select statement to query records as:\r\n\r\n```sql\r\nSELECT * FROM domain_ip_day WHERE domain=\"www.baidu.com.\" AND date=20170801 LIMIT 1000;\r\n```\r\n\r\nIt will spend almost 10s. When I remove `LIMIT` it will spend 70ms\r\n\r\nTable structure is like:\r\n\r\n```\r\nCREATE TABLE domain_ip_day (\r\n    domain varchar(255),\r\n    date int(11),\r\n    ...\r\n    KEY idx_domain_date (`domain`, `date`)\r\n)\r\n```\r\n\r\n\r\n2. What did you expect to see?\r\n\r\n\r\n3. What did you see instead?\r\n\r\nWhen execute `EXPLAIN` statement, will get plan:\r\n\r\n```\r\nmysql> explain select * from domain_ip_day where domain=\"www.baidu.com.\" and date=\"20170809\" limit 1000;\r\n+----------------+-------------+----------------+------+----------------------------------------------------------------------------------------+-------+\r\n| id             | parents     | children       | task | operator info                                                                          | count |\r\n+----------------+-------------+----------------+------+----------------------------------------------------------------------------------------+-------+\r\n| TableScan_7    | Selection_8 |                | cop  | table:domain_ip_day, range:(-inf,+inf), keep order:false                               |  1250 |\r\n| Selection_8    | Limit_13    | TableScan_7    | cop  | eq(hebei.domain_ip_day.domain, www.baidu.com.), eq(hebei.domain_ip_day.date, 20170809) |  1000 |\r\n| Limit_13       |             | Selection_8    | cop  | offset:0, count:1000                                                                   |  1000 |\r\n| TableReader_14 | Limit_6     |                | root | data:Limit_13                                                                          |  1000 |\r\n| Limit_6        |             | TableReader_14 | root | offset:0, count:1000                                                                   |  1000 |\r\n+----------------+-------------+----------------+------+----------------------------------------------------------------------------------------+-------+\r\n```\r\n\r\nWhen I modify `LIMIT` size up to 1274 will got plan:\r\n\r\n```\r\nmysql> explain select * from domain_ip_day where domain=\"www.baidu.com.\" and date=\"20170809\" limit 1274;\r\n+----------------+---------+----------------+------+---------------------------------------------------------------------------------------------------------------------+-----------+\r\n| id             | parents | children       | task | operator info                                                                                                       | count     |\r\n+----------------+---------+----------------+------+---------------------------------------------------------------------------------------------------------------------+-----------+\r\n| IndexScan_26   |         |                | cop  | table:domain_ip_day, index:domain, date, range:[www.baidu.com. 20170809,www.baidu.com. 20170809], out of order:true | 891.57756 |\r\n| TableScan_27   |         |                | cop  | table:domain_ip_day, keep order:false                                                                               | 891.57756 |\r\n| IndexLookUp_28 | Limit_6 |                | root | index:IndexScan_26, table:TableScan_27                                                                              | 891.57756 |\r\n| Limit_6        |         | IndexLookUp_28 | root | offset:0, count:1274                                                                                                |      1274 |\r\n+----------------+---------+----------------+------+---------------------------------------------------------------------------------------------------------------------+-----------+\r\n4 rows in set (0.00 sec)\r\n```\r\n\r\n4. What version of TiDB are you using (`tidb-server -V`)?\r\n\r\nRC4\r\n"},"comment":{"url":"https://api.github.com/repos/pingcap/tidb/issues/comments/323235089","html_url":"https://github.com/pingcap/tidb/issues/4188#issuecomment-323235089","issue_url":"https://api.github.com/repos/pingcap/tidb/issues/4188","id":323235089,"user":{"login":"blacktear23","id":209576,"avatar_url":"https://avatars2.githubusercontent.com/u/209576?v=4","gravatar_id":"","url":"https://api.github.com/users/blacktear23","html_url":"https://github.com/blacktear23","followers_url":"https://api.github.com/users/blacktear23/followers","following_url":"https://api.github.com/users/blacktear23/following{/other_user}","gists_url":"https://api.github.com/users/blacktear23/gists{/gist_id}","starred_url":"https://api.github.com/users/blacktear23/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/blacktear23/subscriptions","organizations_url":"https://api.github.com/users/blacktear23/orgs","repos_url":"https://api.github.com/users/blacktear23/repos","events_url":"https://api.github.com/users/blacktear23/events{/privacy}","received_events_url":"https://api.github.com/users/blacktear23/received_events","type":"User","site_admin":false},"created_at":"2017-08-18T01:22:01Z","updated_at":"2017-08-18T01:22:01Z","body":"After I execute `SHOW STATS_BUCKETS;` the result do not contains `domain_ip_day ` table."}}
{"actor":{"display_login":"blacktear23"}}