- Notifications
You must be signed in to change notification settings - Fork6.1k
Open
Description
Enhancement
There is the following case:
CREATE TABLE test_table ( id INT AUTO_INCREMENT PRIMARY KEY, c1 INT NOT NULL, c2 INT NOT NULL, other_data VARCHAR(255), INDEX idx_c1_c2 (c1, c2));EXPLAIN SELECT * FROM test_table WHERE c1 IN (0, 1, 2, 3, 4) AND (c2 != -1 AND NOT (c2 = 7 AND c1 = 2));+---------------------------+---------+-----------+-------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| id | estRows | task | access object | operator info |+---------------------------+---------+-----------+-------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| IndexLookUp_9 | 295.63 | root | | || ├─Selection_8(Build) | 295.63 | cop[tikv] | | or(ne(test.test_table.c2, 7), ne(test.test_table.c1, 2)) || │ └─IndexRangeScan_6 | 332.83 | cop[tikv] | table:test_table, index:idx_c1_c2(c1, c2) | range:[0 -inf,0 -1), (0 -1,0 +inf], [1 -inf,1 -1), (1 -1,1 +inf], [2 -inf,2 -1), (2 -1,2 +inf], [3 -inf,3 -1), (3 -1,3 +inf], [4 -inf,4 -1), (4 -1,4 +inf], keep order:false, stats:pseudo || └─TableRowIDScan_7(Probe) | 295.63 | cop[tikv] | table:test_table | keep order:false, stats:pseudo |+---------------------------+---------+-----------+-------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+Now, TiDB can build rangewith log_category IN (0, 1, 2, 3, 4) and log_status != -1
"[0 -inf,0 -1)","(0 -1,0 +inf]","[1 -inf,1 -1)","(1 -1,1 +inf]","[2 -inf,2 -1)", <-"(2 -1,2 +inf]", <-"[3 -inf,3 -1)","(3 -1,3 +inf]","[4 -inf,4 -1)","(4 -1,4 +inf]"But, we should get better range with all expressions.
"[0 -inf,0 -1)","(0 -1,0 +inf]","[1 -inf,1 -1)","(1 -1,1 +inf]", "[2 -inf,2 -1)", <-"[2 0, 2 7)", <-"(2 8,2 +inf]", <-"[3 -inf,3 -1)","(3 -1,3 +inf]","[4 -inf,4 -1)","(4 -1,4 +inf]"It can reduce the scan.