Optimize SQL for handle ID query

[dawnbreaks]

Feature Request

CREATE TABLE `test`.`id_test` (
  `id` varchar(64) NOT NULL,
  `name` varchar(64) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

explain SELECT * from test.id_test where _tidb_rowid = 1;
explain SELECT * from test.id_test where _tidb_rowid >= 1 and _tidb_rowid < 100;

Query execution plan for the above SQL are not acceptable, TableScan is too slow and expensive. It 's expected that TIDB should use the handle ID(_tidb_rowid) to access the the raw table data directly!

Projection_4	5.60	root	test.id_test.id, test.id_test.name
└─TableReader_7	5.60	root	data:Selection_6
  └─Selection_6	5.60	cop	ge(test.id_test._tidb_rowid, 1), lt(test.id_test._tidb_rowid, 100)
    └─TableScan_5	7.00	cop	table:id_test, range:[-inf,+inf], keep order:false, stats:pseudo

[XuHuaiyu]

Hi, @dawnbreaks
What's your tidb_version?
You can run sql select tidb_version() to get it.

[shenli]

@dawnbreaks There is no data in your table. So I think it is reasonable for the query plan.
Please try inserting some data into your table and explain the query again.

[dawnbreaks]

@XuHuaiyu The version of TIDB is "Release Version: v2.1.2-1-g8ba8096".

@shenli We have a table which has more than 100 million rows, and the query execution is the same as above.

[XuHuaiyu]

Hi, @dawnbreaks , we've fixed it in this PR.
#8047

But it may not be cherry-picked to version 2.1.

[dawnbreaks]

Is there a plan to cherry-pick it to version 2.1?

[XuHuaiyu]

PTAL @zz-jason @shenli

[zz-jason]

@dawnbreaks already cherry picked to release-2.1, close this issue now.