OR查询时，卡住了

[daokuan]

为提高效率，请提供以下信息，问题描述清晰能够更快得到解决：
【 TiDB 使用环境】

[tidb@jumpserver ~]$ tiup cluster display tidb_prod;
Starting component cluster: /home/tidb/.tiup/components/cluster/v1.9.0/tiup-cluster display tidb_prod
Cluster type: tidb
Cluster name: tidb_prod
Cluster version: v5.3.0
Deploy user: tidb
SSH type: builtin
Dashboard URL: http://192.168.0.66:2379/dashboard
ID
【概述】 场景 + 问题概述
一个带有查询不能出来结果

【背景】 做过哪些操作

【现象】 业务和数据库现象
用这个查询，tidb hold住很久（超过一小时）也不能出结果：
select cheliang_intermediate., gasj_freight_intermediate. from traffic_stats.cheliang_intermediate, traffic_stats.gasj_freight_intermediate where (cheliang_intermediate.plate_for_ga_1 = gasj_freight_intermediate.hphm or cheliang_intermediate.plate_for_ga_2 = gasj_freight_intermediate.hphm) limit 1;

而用这个查询：
select cheliang_valid_intermediate., gasj_freight_intermediate. from traffic_stats.cheliang_valid_intermediate, traffic_stats.gasj_freight_intermediate where (cheliang_valid_intermediate.plate_for_ga_2 = gasj_freight_intermediate.hphm ) limit 1;
或者这个查询：
select cheliang_valid_intermediate., gasj_freight_intermediate. from traffic_stats.cheliang_valid_intermediate, traffic_stats.gasj_freight_intermediate where (cheliang_valid_intermediate.plate_for_ga_1 = gasj_freight_intermediate.hphm ) limit 1;

结果秒出。

看起来是有bug了。
其中，数据库的schema：

show create table cheliang_valid_intermediate;
±----------------------------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±----------------------------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| cheliang_valid_intermediate | CREATE TABLE cheliang_valid_intermediate (
plate varchar(32) NOT NULL,
plate_for_ga_1 varchar(32) NOT NULL,
plate_for_ga_2 varchar(32) NOT NULL,
color varchar(8) NOT NULL,
ton double NOT NULL,
raw_province varchar(16) NOT NULL,
raw_city varchar(32) NOT NULL,
raw_county varchar(32) NOT NULL,
PRIMARY KEY (plate) /*T![clustered_index] NONCLUSTERED */,
KEY plate1 (plate_for_ga_1),
KEY plate2 (plate_for_ga_2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
±----------------------------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

show create table gasj_freight_intermediate;
±--------------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±--------------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| gasj_freight_intermediate | CREATE TABLE gasj_freight_intermediate (
hphm varchar(32) NOT NULL,
GXSJ datetime DEFAULT NULL,
power_type varchar(6) NOT NULL,
axle_count int(11) NOT NULL,
veh_type varchar(6) NOT NULL,
self_weight double NOT NULL,
PRIMARY KEY (hphm) /*T![clustered_index] NONCLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
±--------------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

【问题】 当前遇到的问题

【业务影响】

【TiDB 版本】

【应用软件及版本】

【附件】 相关日志及配置信息

• TiUP Cluster Display 信息
• TiUP CLuster Edit config 信息

监控（https://metricstool.pingcap.com/)

• TiDB-Overview Grafana监控
• TiDB Grafana 监控
• TiKV Grafana 监控
• PD Grafana 监控
• 对应模块日志（包含问题前后 1 小时日志）

---

若提问为性能优化、故障排查类问题，请下载脚本运行。终端输出的打印结果，请务必全选并复制粘贴上传。

[h5n1]

用or的执行计划贴下

[啦啦啦啦啦]

看看tidb_enable_index_merge是否开启

[Christophe (Doudouwyh)]

explain 贴一下吧

[TiDBer_L3dm6WTj]

应该不是bug，这些表的数据量有多大？

[TiDBer_4oMELAVl]

这种bug应该不会有，我觉得还是从数据库表结构和查询语句上入手

[疾风之狼]

不能完全指望优化器，优化器也有打盹的时候，如果我们自己多写点代码，比放手让优化器去优化更好一点，比如您的SQL改成嵌套union的方式：
select * from (
select cheliang_valid_intermediate. , gasj_freight_intermediate. from traffic_stats.cheliang_valid_intermediate, traffic_stats.gasj_freight_intermediate where (cheliang_valid_intermediate.plate_for_ga_2 = gasj_freight_intermediate.hphm ) limit 1;
union
select cheliang_valid_intermediate. , gasj_freight_intermediate. from traffic_stats.cheliang_valid_intermediate, traffic_stats.gasj_freight_intermediate where (cheliang_valid_intermediate.plate_for_ga_1 = gasj_freight_intermediate.hphm )
)limit 1;