Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: tidb执行计划不全的问题
|
username: zhanggame1
[TiDB Usage Environment] Production Environment / Testing / Poc
[TiDB Version] 7.1
Why does the execution plan for a complex statement have so little detail?
The execution plan for the subquery part of the complex statement, however, has a lot of details.
I switched to another 7.1 version test database, and the result is the same.
Could you provide the table structure so we can reproduce it?
Actually, it feels like the execution plan is correct, even though it involves a lot of data from a single table. However, the execution plan for the g table is missing, so we don’t know what kind of table g is.
This statement involves two tables: game_draw and SCHEDULE.
|
username: 
|
username: CREATE TABLE `game_draw` (
`draw_id` int(11) NOT NULL AUTO_INCREMENT,
`game_id` int(11) NOT NULL,
`draw_year` int(11) NOT NULL,
`draw_no` varchar(5) NOT NULL,
`sale_begin_time` datetime NOT NULL,
`sale_end_time` datetime NOT NULL,
`draw_status` tinyint(4) NOT NULL,
`draw_type` tinyint(4) NOT NULL DEFAULT '0',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`draw_result` varchar(4000) DEFAULT NULL,
`prize_calculation` tinyint(4) NOT NULL DEFAULT '0',
`suspend_status` tinyint(4) NOT NULL DEFAULT '0',
`draw_time` datetime DEFAULT NULL,
`paid_begin_time` datetime DEFAULT NULL,
`paid_end_time` datetime DEFAULT NULL,
`game_type` int(11) NOT NULL,
PRIMARY KEY (`draw_id`) /*T![clustered_index] CLUSTERED */
);
CREATE TABLE `schedule` (
`schedule_id` int(11) NOT NULL AUTO_INCREMENT,
`draw_id` int(11) NOT NULL,
`matchup_id` int(11) NOT NULL,
`matchup_no` smallint(6) NOT NULL,
`handicap` decimal(10,1) DEFAULT NULL,
`sale_status` tinyint(4) NOT NULL,
`suspend_status` int(11) NOT NULL DEFAULT '0',
`draw_flag` tinyint(4) unsigned zerofill NOT NULL DEFAULT '0',
`paid_end_time` datetime DEFAULT NULL,
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`draw_matchup_order_no` smallint(6) NOT NULL,
`draw_time` datetime DEFAULT NULL,
`result` int(11) DEFAULT NULL,
`sp_flag` tinyint(4) DEFAULT NULL,
`matchup_unique_no` int(9) NOT NULL,
`sale_end_time` datetime NOT NULL,
`is_deleted` tinyint(4) NOT NULL DEFAULT '0',
`game_id` int(11) NOT NULL,
`game_type` int(11) NOT NULL,
PRIMARY KEY (`schedule_id`) /*T![clustered_index] CLUSTERED */
);
sql:
explain ANALYZE
SELECT
*
FROM
`SCHEDULE` scc
WHERE
scc.matchup_no <(
SELECT
MIN(matchup_no)
FROM
`SCHEDULE` sc
WHERE
sc.draw_id IN (SELECT draw_id FROM game_draw g WHERE g.draw_no = '23002')
AND sc.sale_status = 1
AND sc.game_type = 1200
)
AND scc.draw_id = 337525;
In other version 7.1 databases, creating these two tables also results in the same execution plan for queries with no data.
I was able to reproduce it on my end, and it also occurred in v6.5.3. It seems to be a bug.
I tried versions 7.1, 7.2, and 7.3, and they were all the same.
It’s somewhat like an optimization; it directly calculates the result in the red box. The minimum value of the data I created is 1.
Could it be a hidden buff that automatically rewrote the execution plan, so it didn’t tell you? 
The return value of a subquery that finds the minimum value (min) is definitely just a number. In practice, however, the subquery can be quite complex. Take a look at the execution plan of the subquery in my main post.
Rewriting to a subquery execution plan is not necessary… The main post has a subquery execution plan, which is still relatively complex.
Adding “all” to scc.matchup_no < all(…) can display the execution plan of the subquery.
|
username: 
|
username: 
|
username: 
|
username: