# `IndexJoin` and `IndexHashJoin` Execution Plan 

__*Need more guides from extracting from the speaker notes to bullets*__
__*Index Lookup Reader nowhere to find?*__

+ After filtering the **`first_name`** column in the **`employees`** table, the resulting dataset is relatively small and serves as the driving table in the join operation
+ The **`salaries`** table has an index, **`idx_emp_no`**, on the join column **`emp_no`**, which can act as the driven table

```6
tidb> EXPLAIN SELECT * FROM employees, salaries WHERE employees.emp_no = salaries.emp_no AND employees.first_name LIKE 'T%';
+----------------------------------+-----------+-----------+------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                               | estRows   | task      | access object                            | operator info                                                                                                                                             |
+----------------------------------+-----------+-----------+------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| ~!@IndexHashJoin_14@!~                 | 146243.91 | root      |                                          | inner join, inner:IndexLookUp_11, outer key:db1.employees.emp_no, inner key:db1.salaries.emp_no, equal cond:eq(db1.employees.emp_no, db1.salaries.emp_no) |
| ├─TableReader_43(Build)          | 15529.98  | root      |                                          | data:Selection_42                                                                                                                                         |
| │ └─Selection_42                 | 15529.98  | cop[tikv] |                                          | like(db1.employees.first_name, "T%", 92)                                                                                                                  |
| │   └─TableFullScan_41           | 300024.00 | cop[tikv] | table:employees                          | keep order:false                                                                                                                                          |
| └─~!@IndexLookUp_11(Probe)@!~          | 146243.91 | root      |                                          |                                                                                                                                                           |
|   ├─IndexRangeScan_9(Build)      | 146243.91 | cop[tikv] | table:salaries, index:idx_emp_no(emp_no) | range: decided by [eq(db1.salaries.emp_no, db1.employees.emp_no)], keep order:false                                                                       |
|   └─TableRowIDScan_10(Probe)     | 146243.91 | cop[tikv] | table:salaries                           | keep order:false                                                                                                                                          |
+----------------------------------+-----------+-----------+------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)
```

------------------------------------------------------------------------------
According to the execution plan on the slide, when the condition `employees.first_name LIKE 'T%'`, the number of rows involved in the join is reduced, prompting the optimizer to use `IndexHashJoin`.

The sequence of operations is as follows:
1. `TableFullScan_41` at line 4 performs a full table scan on TiKV to read the `employees` table.
2. `Selection_42` at line 3 filters the rows based on "`employees.first_name LIKE 'T%'`" and returns the matching rows to the TiDB Server.
3. `IndexRangeScan_9(Build)` at line 6 reads rows from the `salaries` table using an index scan based on `emp_no`.
4. `IndexLookUp_11(Probe)` at line 5 performs an index lookup based on the rows obtained from the `employees` table.
5. Finally, `IndexHashJoin_14` at line 1 completes the join operation.

Characteristics of `IndexJoin` or `IndexHashJoin`:
1. Time Complexity: The time complexity is roughly proportional to the total number of rows in the participating tables. It has less impact on caching compared to `HashJoin` and `MergeJoin`.
2. Parallel Execution: This method can be executed in parallel, enhancing performance.
3. Network Latency: Index lookups may introduce network latency. `IndexJoin` is less suitable for large driving tables in distributed databases.

Table Definitions:

Table `employees`:
```
CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`) /*T![clustered_index] CLUSTERED */,
  KEY `idx_first_name` (`first_name`),
  KEY `idx_last_name` (`last_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
```

Table `salaries`:
```
CREATE TABLE `salaries` (
  `emp_no` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  KEY `idx_emp_no` (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
```