# `IndexMerge` Data Aggregator

+ **`IndexMerge`** is an extension of **`IndexLookupReader`**, it combines multiple indexes scans `(Build)` to work with a single `(Probe)` to get the row set

```
tidb> EXPLAIN SELECT * FROM employees WHERE first_name='Peter' OR last_name='Candy';
+----------------------------+--------+---------------+---------------------------------------------------+-------------------------------------------+
| id                         | estRows| task          | access object                                     | operator info                             |
+----------------------------+--------+---------------+---------------------------------------------------+-------------------------------------------+
| IndexMerge_11              | 420.16 | root          |                                                   | type: union                               |
| ├─~!@IndexRangeScan_8(Build)@!~  | 236.39 | cop[tikv]     | table:employees, index:idx_first_name(first_name) | range:["Peter","Peter"], keep order:false |
| ├─~!@IndexRangeScan_9(Build)@!~  | 183.92 | cop[tikv]     | table:employees, index:idx_last_name(last_name)   | range:["Candy","Candy"], keep order:false |
| └─TableRowIDScan_10(Probe) | 420.16 | cop[tikv]     | table:employees                                   | keep order:false                          |
+----------------------------+--------+---------------+---------------------------------------------------+-------------------------------------------+
4 rows in set (0.00 sec)
```

------------------------------------------------------------------------------
Typically, a SQL statement can use only one index for filtering, except in cases involving table joins. However, this SQL statement appears to be an exception. The filtering conditions are `first_name='Peter' OR last_name='Candy'`, which leads to the following execution steps:

1. `IndexRangeScan_8(Build)`: Scans the `idx_first_name(first_name)` index for matches on `first_name`.
2. `IndexRangeScan_9(Build)`: Scans the `idx_last_name(last_name)` index for matches on `last_name`.

Both `IndexRangeScan` steps operate in TiKV, scanning their respective indexes. The results from these scans, which meet the conditions, are then aggregated using a union to account for the `OR` condition. 

3. `TableRowIDScan_10(Probe)`: Retrieves the corresponding value rows from TiKV and sends them to the TiDB Server.

Finally, `IndexMerge_11` completes the operation and returns the result to the client. This method, known as `IndexMerge`, allows for filtering using multiple indexes when there is a union in the `WHERE` clause. It is positioned after a full table scan not due to inefficiency, but because of its unique operation.

In the execution plan:

```
├─IndexRangeScan_8(Build)
├─IndexRangeScan_9(Build)
└─TableRowIDScan_10(Probe)
```

The terms `Build` and `Probe` indicate the data flow process. Operations marked as `Build` are executed in parallel first, followed by the `Probe` operation, which aggregates the results from the `Build` operations. Understanding these terms helps clarify the order and flow of steps in the execution plan.