planner: mismatched integer and varchar value types prevent the optimizer from choosing index range scan
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Enhancement
See the case below, the second query can't use the IndexRangeScan, because the value type and index column type are mismatched.
This is a common problem for many users who are using ORM inappropriately.
```
CREATE TABLE t_user (
user_code VARCHAR(32) NOT NULL,
KEY idx_user_code(user_code)
);
EXPLAIN SELECT * FROM t_user WHERE user_code IN (1001,1002,1003);
+-------------------------+----------+-----------+----------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------+----------+-----------+----------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| IndexReader_10 | 9920.00 | root | | index:Selection_9 |
| └─Selection_9 | 9920.00 | cop[tikv] | | or(eq(cast(test.t_user.user_code, double BINARY), 1001), or(eq(cast(test.t_user.user_code, double BINARY), 1002), eq(cast(test.t_user.user_code, double BINARY), 1003))) |
| └─IndexFullScan_8 | 10000.00 | cop[tikv] | table:t_user, index:idx_user_code(user_code) | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+----------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
EXPLAIN SELECT * FROM t_user WHERE user_code IN ('1001','1002','1003');
+------------------------+---------+-----------+----------------------------------------------+-----------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------+---------+-----------+----------------------------------------------+-----------------------------------------------------------------------------------------+
| IndexReader_6 | 30.00 | root | | index:IndexRangeScan_5 |
| └─IndexRangeScan_5 | 30.00 | cop[tikv] | table:t_user, index:idx_user_code(user_code) | range:["1001","1001"], ["1002","1002"], ["1003","1003"], keep order:false, stats:pseudo |
+------------------------+---------+-----------+----------------------------------------------+-----------------------------------------------------------------------------------------+
```
Need to also consider Plan Cache:
```
mysql> prepare st from "select * from t_user where user_code in (?,?)";
Query OK, 0 rows affected (0.004 sec)
mysql> set @a=1001, @b=1002;
Query OK, 0 rows affected (0.002 sec)
mysql> execute st using @a, @b;
Empty set (0.011 sec)
mysql> explain for connection 1983905798;
+-------------------------+----------+---------+-----------+----------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------+-----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-------------------------+----------+---------+-----------+----------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------+-----------+------+
| IndexReader_10 | 9600.00 | 0 | root | | time:7.12ms, open:70.4µs, close:8µs, loops:1, cop_task: {num: 1, max: 1.66ms, proc_keys: 0, tot_proc: 61.3µs, tot_wait: 200.5µs, copr_cache_hit_ratio: 0.00, build_task_duration: 18.7µs, max_distsql_concurrency: 1}, fetch_resp_duration: 6.98ms, rpc_info:{Cop:{num_rpc:2, total_time:3.41ms}, rpc_errors:{epoch_not_match:1}}, backoff{regionMiss: 2ms} | index:Selection_9 | 262 Bytes | N/A |
| └─Selection_9 | 9600.00 | 0 | cop[tikv] | | tikv_task:{time:1ms, loops:1}, scan_detail: {total_keys: 1, get_snapshot_time: 171.6µs, rocksdb: {block: {}}}, time_detail: {total_process_time: 61.3µs, total_wait_time: 200.5µs, total_kv_read_wall_time: 1ms, tikv_grpc_process_time: 56.7µs, tikv_grpc_wait_time: 22.6µs, tikv_wall_time: 993.1µs} | or(eq(cast(test.t_user.user_code, double BINARY), 1001), eq(cast(test.t_user.user_code, double BINARY), 1002)) | N/A | N/A |
| └─IndexFullScan_8 | 10000.00 | 0 | cop[tikv] | table:t_user, index:idx_user_code(user_code) | tikv_task:{time:1ms, loops:1} | keep order:false, stats:pseudo | N/A | N/A |
+-------------------------+----------+---------+-----------+----------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------+-----------+------+
```
Contributor guide
Assessment
This issue has not been assessed yet.