Repository navigation
planner: can't use primary as an index in IndexMerge for predicate id=? and (a=? or b=?) #66668
Copy link
Copy link
Closed
Labels
affects-8.5This bug affects the 8.5.x(LTS) versions.This bug affects the 8.5.x(LTS) versions.plan-rewritereport/customerCustomers have encountered this bug.Customers have encountered this bug.sig/plannerSIG: PlannerSIG: Plannertype/enhancementThe issue or PR belongs to an enhancement.The issue or PR belongs to an enhancement.
Description
Enhancement
See the example below, we can use IndexMerge for the second plan, but can't for the first plan:
create table if not exists t (
id int,
a int,
b int,
primary key (id, a),
key ka (id, a),
key kb (id, b)
);
explain select /*+ use_index_merge(t, primary, kb) */ * from t where id=1 and (a=1 or b=1);
explain select /*+ use_index_merge(t, ka, kb) */ * from t where id=1 and (a=1 or b=1);
mysql> explain select /*+ use_index_merge(t, primary, kb) */ * from t where id=1 and (a=1 or b=1);
+--------------------------+---------+-----------+---------------+---------------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------+---------+-----------+---------------+---------------------------------------------+
| TableReader_8 | 1.00 | root | | data:Selection_7 |
| └─Selection_7 | 1.00 | cop[tikv] | | or(eq(test.t.a, 1), eq(test.t.b, 1)) |
| └─TableRangeScan_6 | 10.00 | cop[tikv] | table:t | range:[1,1], keep order:false, stats:pseudo |
+--------------------------+---------+-----------+---------------+---------------------------------------------+
3 rows in set, 1 warning (0.002 sec)
mysql> explain select /*+ use_index_merge(t, ka, kb) */ * from t where id=1 and (a=1 or b=1);
+-------------------------------+---------+-----------+--------------------------+--------------------------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------------+---------+-----------+--------------------------+--------------------------------------------------------+
| IndexMerge_10 | 0.00 | root | | type: union |
| ├─IndexRangeScan_6(Build) | 0.10 | cop[tikv] | table:t, index:ka(id, a) | range:[1 1,1 1], keep order:false, stats:pseudo |
| ├─IndexRangeScan_7(Build) | 0.10 | cop[tikv] | table:t, index:kb(id, b) | range:[1 1,1 1], keep order:false, stats:pseudo |
| └─Selection_9(Probe) | 0.00 | cop[tikv] | | eq(test.t.id, 1), or(eq(test.t.a, 1), eq(test.t.b, 1)) |
| └─TableRowIDScan_8 | 8.00 | cop[tikv] | table:t | keep order:false, stats:pseudo |
+-------------------------------+---------+-----------+--------------------------+--------------------------------------------------------+
Reactions are currently unavailable
Metadata
Metadata
Assignees
Labels
affects-8.5This bug affects the 8.5.x(LTS) versions.This bug affects the 8.5.x(LTS) versions.plan-rewritereport/customerCustomers have encountered this bug.Customers have encountered this bug.sig/plannerSIG: PlannerSIG: Plannertype/enhancementThe issue or PR belongs to an enhancement.The issue or PR belongs to an enhancement.
Activity
pkg/planner: allow primary key as IndexMerge partial path for `id=? a…
pkg/planner: allow primary key as IndexMerge partial path for `id=? a…