Skip to content

planner: can't use primary as an index in IndexMerge for predicate id=? and (a=? or b=?) #66668

Closed

Description

@qw4990

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                         |
+-------------------------------+---------+-----------+--------------------------+--------------------------------------------------------+

Activity

  1. added a commit that references this issue on Mar 18, 2026
    943819a
  2. added a commit that references this issue on Mar 23, 2026
    52866fa
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    affects-8.5This bug affects the 8.5.x(LTS) versions.plan-rewritereport/customerCustomers have encountered this bug.sig/plannerSIG: Plannertype/enhancementThe issue or PR belongs to an enhancement.

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions