# `TiFlash MPP` Process - Cont'd

![`smart`](MPP04_aa_003_20220512.png)

------------------------------------------------------------------------------
In the slide, an important step involves exchanging the filtered data in memory based on the join column `pid`. This process ensures that rows with the same `pid` from both the `order` and `product` tables are placed in the memory of the same TiFlash columnar store node. Two algorithms are used for this data exchange:

`Shuffled Hash Join`:
1. Partitioning: Divide the data sets to be joined into two parts, typically referred to as the left table and the right table. In this example, `product` is the left table and `order` is the right table.
2. Hashing: Create hash tables for both tables based on the join column. For instance, hash tables are built for the `pid` column in both `product` and `order` using a hash function, such as modulo 10.
3. Partitioning by Hash Value: Choose one table (typically the one with fewer rows) as the driving table. Split this table into multiple partitions based on hash values of the join column. In this case, `product` is chosen as the driving table.
4. Data Exchange: Send each partition of the right table to the corresponding partition of the left table. For the slide example, the `order` table partitions are sent to match the hash partitions of the `product` table.

`Shuffled Hash Join` efficiently groups and matches join column values, making it suitable for large datasets and leveraging distributed computing to accelerate queries.

`Broadcast Hash Join`:
1. Broadcasting: Choose the smaller table (the broadcast table) and replicate it across all execution nodes. The larger table is the non-broadcast table.
2. Hashing: Perform hash operations on both the broadcast and non-broadcast tables to generate hash tables, distributing the data based on join column values.
3. Join Operation: Each node performs the join operation between its copy of the non-broadcast table and the broadcast table using the hash table to quickly locate matching rows.

`Broadcast Hash Join` reduces network data transfer and enhances join performance by maximizing parallelism in a distributed environment. It broadcasts a complete copy of the smaller table to all nodes, allowing efficient joins via hash tables.

In an MPP architecture, `Shuffled Hash Join` is generally preferred for large tables. However, the choice between `Shuffled Hash Join` and `Broadcast Hash Join` can be controlled using system variables:
- `tidb_broadcast_join_threshold_size`: This variable determines the table size threshold (default is 100MB). If a table is smaller than this threshold, `Broadcast Hash Join` is used; otherwise, `Shuffled Hash Join` is chosen.
- `tidb_broadcast_join_threshold_count`: This variable sets a row count threshold. For subqueries where the size is unknown, the row count is used to determine the join method. If the estimated row count is below this threshold, `Broadcast Hash Join` is used; otherwise, `Shuffled Hash Join` is selected.