optimizer cannot handle cases like ((a = 1 and b = 2 and c > 3) or (a = 4 and b = 5 and c > 6)) and d > 3

[xuyifangreeneyes]

Enhancement

mysql> create table t1(a int, b int, c int, d int, index idx(a, b, c));
Query OK, 0 rows affected (0.06 sec)

mysql> explain select * from t1 where (a = 1 and b = 2 and c > 3) or (a = 4 and b = 5 and c > 6)
    -> ;
+-------------------------------+---------+-----------+------------------------------+--------------------------------------------------------------------------+
| id                            | estRows | task      | access object                | operator info                                                            |
+-------------------------------+---------+-----------+------------------------------+--------------------------------------------------------------------------+
| IndexLookUp_10                | 0.67    | root      |                              |                                                                          |
| ├─IndexRangeScan_8(Build)     | 0.67    | cop[tikv] | table:t1, index:idx(a, b, c) | range:(1 2 3,1 2 +inf], (4 5 6,4 5 +inf], keep order:false, stats:pseudo |
| └─TableRowIDScan_9(Probe)     | 0.67    | cop[tikv] | table:t1                     | keep order:false, stats:pseudo                                           |
+-------------------------------+---------+-----------+------------------------------+--------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> explain select * from t1 where ((a = 1 and b = 2 and c > 3) or (a = 4 and b = 5 and c > 6)) and d > 3;
+----------------------------+---------+-----------+------------------------------+------------------------------------------------------------------------------------------------------------------------------------+
| id                         | estRows | task      | access object                | operator info                                                                                                                      |
+----------------------------+---------+-----------+------------------------------+------------------------------------------------------------------------------------------------------------------------------------+
| IndexLookUp_12             | 0.22    | root      |                              |                                                                                                                                    |
| ├─Selection_10(Build)      | 0.22    | cop[tikv] |                              | or(and(eq(test.t1.a, 1), and(eq(test.t1.b, 2), gt(test.t1.c, 3))), and(eq(test.t1.a, 4), and(eq(test.t1.b, 5), gt(test.t1.c, 6)))) |
| │ └─IndexRangeScan_8       | 20.00   | cop[tikv] | table:t1, index:idx(a, b, c) | range:[1,1], [4,4], keep order:false, stats:pseudo                                                                                 |
| └─Selection_11(Probe)      | 0.22    | cop[tikv] |                              | gt(test.t1.d, 3)                                                                                                                   |
|   └─TableRowIDScan_9       | 0.22    | cop[tikv] | table:t1                     | keep order:false, stats:pseudo                                                                                                     |
+----------------------------+---------+-----------+------------------------------+------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

The second query can also build (1 2 3,1 2 +inf], (4 5 6,4 5 +inf] but currently we only build [1,1], [4,4].

[xuyifangreeneyes]

Here is another example:

mysql> create table t2(a int, b varchar(20), c int, primary key(a, b));
Query OK, 0 rows affected (0.02 sec)

mysql> show create table t2;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                  |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `a` int(11) NOT NULL,
  `b` varchar(20) NOT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`,`b`) /*T![clustered_index] NONCLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain select * from t2 where ((a > 5) or (a = 5 and (b >= 'xx01'))) and ((a < 5) or (a = 5 and (b < 'xx99')));
+-------------------------------+---------+-----------+-------------------------------+----------------------------------------------------------------------------------------------------------------------------------------+
| id                            | estRows | task      | access object                 | operator info                                                                                                                          |
+-------------------------------+---------+-----------+-------------------------------+----------------------------------------------------------------------------------------------------------------------------------------+
| IndexLookUp_11                | 1122.61 | root      |                               |                                                                                                                                        |
| ├─Selection_10(Build)         | 1122.61 | cop[tikv] |                               | or(gt(test.t2.a, 5), and(eq(test.t2.a, 5), ge(test.t2.b, "xx01"))), or(lt(test.t2.a, 5), and(eq(test.t2.a, 5), lt(test.t2.b, "xx99"))) |
| │ └─IndexRangeScan_8          | 1403.26 | cop[tikv] | table:t2, index:PRIMARY(a, b) | range:[5,5], keep order:false, stats:pseudo                                                                                            |
| └─TableRowIDScan_9(Probe)     | 1122.61 | cop[tikv] | table:t2                      | keep order:false, stats:pseudo                                                                                                         |
+-------------------------------+---------+-----------+-------------------------------+----------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.01 sec)

[ghazalfamilyusa]

It does not look like an enhancement to me but probably a bug. The optimizer managed to find the ranges for (a = 1 and b = 2 and c > 3) or (a = 4 and b = 5 and c > 6) but not when we just add a conjunct of (d > 3).