Optimizer choose full table scan when there is a better execution path #56321
Description
Bug Report
1. Minimal reproduce step (Required)
mysql> show create table d\G
*************************** 1. row ***************************
Table: d
Create Table: CREATE TABLE `d` (
`mid` bigint(20) unsigned NOT NULL,
`object_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`ov` longblob DEFAULT NULL,
`version` int(11) unsigned NOT NULL,
PRIMARY KEY (`object_id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=23924932 /*T![auto_id_cache] AUTO_ID_CACHE=1 */
1 row in set (0.00 sec)
mysql> show create table i\G
*************************** 1. row ***************************
Table: i
Create Table: CREATE TABLE `i` (
`object_id` bigint(20) unsigned NOT NULL,
`lid` bigint(20) DEFAULT NULL,
`sid` varbinary(767) DEFAULT NULL,
`cid` bigint(20) DEFAULT NULL,
`version` int(11) unsigned NOT NULL,
PRIMARY KEY (`object_id`) /*T![clustered_index] CLUSTERED */,
KEY `i_1` (`cid`,`sid`,`object_id`),
KEY `i_2` (`lid`,`object_id`),
KEY `i_3` (`cid`,`object_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
1 row in set (0.00 sec)
mysql>
explain SELECT
`d`.*
FROM
`i`
LEFT JOIN `d` ON `i`.`object_id` = `d`.`object_id`
WHERE
`i`.`cid` = 249
ORDER BY
`i`.`object_id`
LIMIT 1000 OFFSET 18000;
2. What did you expect to see? (Required)
The execution plan should use table i as driving table to access table d
3. What did you see instead (Required)
The problem is captured by slow log, it is not reproducible any more.
The issue with this plan is, there is no need to full table scan d. Optimizer should offset 18000 rows from i, take the next 1000 rows, and join them to table d. So maybe full table scan i is necessary, but full table scan d to do a merge join is unnecessary.
4. What is your TiDB version? (Required)
v7.5.1