# `IndexMerge` Data Aggregator - Cont'd

+ Index merge in TiDB has two types: the intersection type (AND expression) and the union type (OR expression)
  + The intersection type is introduced in TiDB v6.5.0, and can be used only when the USE_INDEX_MERGE hint is specified (See next page)
  + If the selectivity of one filter condition is very good,  you may chooses the corresponding index to achieve the ideal execution efficiency  

```7
tidb> EXPLAIN SELECT * FROM employees WHERE first_name='Peter' AND last_name='Candy';
+----------------------------+---------+-----------+-------------------------------------------------+---------------------------------------------+
| id                         | estRows | task      | access object                                   | operator info                               |
+----------------------------+---------+-----------+-------------------------------------------------+---------------------------------------------+
| IndexLookUp_15             | 0.14    | root      |                                                 |                                             |
| ├─IndexRangeScan_12(Build) | 183.92  | cop[tikv] | table:employees, index:idx_last_name(last_name) | ~!@range:["Candy","Candy"]@!~, keep order:false   |
| └─Selection_14(Probe)      | 0.14    | cop[tikv] |                                                 | eq(employees.employees.first_name, "Peter") |  
|  └─TableRowIDScan_13       | 183.92  | cop[tikv] | table:employees                                 | keep order:false                            |
+----------------------------+---------+-----------+-------------------------------------------------+---------------------------------------------+
4 rows in set (0.00 sec)
```

------------------------------------------------------------------------------
In the SQL statement above, despite filtering on two indexed columns, only one index, `idx_last_name(last_name)`, is used. Let's analyze the execution plan:

1. `IndexRangeScan_12(Build)` is the first child step, using the `idx_last_name(last_name)` index to filter rows where `last_name = 'Candy'`. 
2. The next step is `TableRowIDScan_13`, which retrieves the corresponding rows from TiKV.

At first glance, it might seem that the rightmost step, `TableRowIDScan_13`, should execute first. However, when reading the execution plan, if multiple rows are executed in parallel, the upper row in parallel groups takes action first. For example, `IndexRangeScan_12(Build)` and `Selection_14(Probe)` are on the same level, but `TableRowIDScan_13` is under `Selection_14(Probe)`. According to "upper-right" rules, `TableRowIDScan_13` is executed before `Selection_14(Probe)`.

Returning to `IndexRangeScan_12(Build)`, it filters `Key` values for `last_name = 'Candy'` and sends them to the TiDB Server. The TiDB Server then retrieves the `Value` values from TiKV, represented by `TableRowIDScan_13`. After filtering by `last_name`, further filtering is required for `first_name = 'Peter'`, which is performed by `Selection_14(Probe)`. Finally, `IndexLookUp_15` completes the operation.

The SQL statement uses an intersection (`WHERE first_name='Peter' AND last_name='Candy'`) rather than a union. In intersection cases, the optimizer does not prefer to use `IndexMerge`, although it can still use it under certain circumstances. The next slide will provide further examples to illustrate this concept.