# `IndexLookUp` Data Aggregator, `IndexRangeScan` and `TableRowIDScan` Operators - Example

![`smart`](IndexRangeScanAndTableRowIDScanOrder_en_001_20240407.png)

```6
tidb> EXPLAIN SELECT * FROM employees WHERE first_name='Parto';
+---------------------------+---------+-----------+---------------------------------------------------+-------------------------------------------+
| id                        | estRows | task      | access object                                     | operator info                             |
+---------------------------+---------+-----------+---------------------------------------------------+-------------------------------------------+
| IndexLookUp_10            | 4.03    | root      |                                                   |                                           |
| ├─IndexRangeScan_8(Build) | 4.03    | cop[tikv] | table:employees, index:idx_first_name(first_name) | range:["Parto","Parto"], keep order:false |
| └─~!@TableRowIDScan_9(Probe) | 4.03    | cop[tikv] | table:employees@!~                                   | keep order:false                          |
+---------------------------+---------+-----------+---------------------------------------------------+-------------------------------------------+

tidb> EXPLAIN SELECT * FROM employees WHERE first_name LIKE 'Par%';
+---------------------------+---------+-----------+---------------------------------------------------+-------------------------------------------+
| id                        | estRows | task      | access object                                     | operator info                             |
+---------------------------+---------+-----------+---------------------------------------------------+-------------------------------------------+
| IndexLookUp_10            | 236.44  | root      |                                                   |                                           |
| ├─IndexRangeScan_8(Build) | 236.44  | cop[tikv] | table:employees, index:idx_first_name(first_name) | range:["Part","Paru"), keep order:false   |
| └─~!@TableRowIDScan_9(Probe) | 236.44  | cop[tikv] | table:employees@!~                                   | keep order:false                          |
+---------------------------+---------+-----------+---------------------------------------------------+-------------------------------------------+
```

------------------------------------------------------------------------------
The `WHERE` predicates in these statements can be queried using the secondary index `idx_first_name`. This method resembles the combination of `IndexReader` and `IndexRangeScan`, but differs in that, after filtering index values that meet the conditions on TiKV nodes, the key values of the corresponding rows are retrieved and sent to the TiDB Server for aggregation. These key values may include one or more entries.

The subsequent operations differ from the `IndexReader` and `IndexRangeScan` combination. In the example `SELECT` clauses, all the SQL statements use `SELECT *`, indicating that all columns are selected. When the TiDB Server receives the key values from the TiKV nodes, it must query the `employees` table to retrieve the corresponding values. Since the secondary index `idx_first_name` only provides the `first_name` key values, a secondary lookup is necessary to obtain the full row data from the table. This process, known as a table lookup, involves accessing both the index and the table, which affects performance.

We can see that the first row in the execution plan is `IndexLookUp`, and it has steps on its right side. So, applying the "execute the rows on the right first" rule, we move to the second row. Since the second and third rows are at the same level, the second part of the mnemonic comes into play: "if two rows are at the same level, execute the upper one first". Now let's interpret it: first, the second row, `IndexRangeScan`, is executed. At this point, the key values (key-value pairs) that satisfy the filtering condition of the secondary index `idx_first_name` are sent from the TiKV nodes to the TiDB Server. TiDB Server then uses these key values to return to the TiKV nodes and read the corresponding values from the table, which is represented by the third row, `TableRowIDScan`. We can see that the task of `TableRowIDScan` is `cop[tikv]`, indicating that it occurs on the TiKV nodes. Finally, the TiKV nodes return the values that meet the conditions to the TiDB Server, and the TiDB Server completes the operation of the first row, which we call an `IndexLookup`.

The primary action in the execution plan is `IndexLookUp`, which has child steps. Since the second and third actions (child actions) are at the same level, the action on the second row, `IndexRangeScan`, is executed first. During this process, key values that meet the filtering condition of the secondary index `idx_first_name` are sent from the TiKV nodes to the TiDB Server. The TiDB Server then uses these key values to query the TiKV nodes and read the corresponding rows from the table, represented by the third row, `TableRowIDScan`. The task of `TableRowIDScan` is executed by TiKV coprocessors, `cop[tikv]`, indicating that it occurs on the TiKV nodes. 

Finally, the TiKV nodes return the values that meet the conditions to the TiDB Server, completing the `IndexLookup` data aggregator operation.