# Working with Joins - Case Study

![`smart`](OptimizerHints05_en_001_20240207.png)

----------------------------------------------------
Our executed SQL query is:
```
SELECT COUNT(*) FROM TA, TB, TC, TD WHERE TA.col1 = TB.col1 AND TA.col2 = TC.col2 AND TC.col2 = TD.col2;
```

Next, we access the `SQL Statement` function in the `TiDB Dashboard`, locate the recently executed query, and examine its EXPLAIN ANALYZE output. We observed that the optimizer chose the join order `TA` - `TB` - `TC` - `TD`. The `actRows` value for the result set after joining `TA`, `TB`, and `TC` is 180,000. However, this value dramatically decreases to 1,800—just 1% of the original—after joining the result set with `TD`. As mentioned in previous courses, when the `actRows` value for the result set of a join operator is significantly smaller than that of its child operator, it often indicates suboptimal performance. This issue is evident in the current SQL query, where the intermediate result set size is problematic. Additionally, we found that the `estRows` value for the result set after joining `TA`, `TB`, and `TC` is 180,000, yet it inexplicably rises to 3,564,356.44 after joining with `TD`. This reveals an inaccuracy in the optimizer's estimation.

In the previous slide, we found that:

```
tidb>SELECT COUNT(*) FROM TC JOIN TD ON(TC.col2=TD.col2);
+--------------+
| COUNT(*)     |
+--------------+
|       15     |
+--------------+
1 row in set (0.01 sec)

tidb>SELECT COUNT(*) FROM TA JOIN TD ON(TA.col2=TD.col2);
+--------------+
| COUNT(*)     |
+--------------+
|       10     |
+--------------+
1 row in set (0.01 sec)
```

In other words, if table `TA` is initially joined with table `TD` or `TC`, the size of the intermediate result set can be minimized. We now aim to change the join order of the tables to `TA`-`TD`-`TC`-`TB`. Initially, we observe that the join conditions are `WHERE TA.col1=TB.col1 AND TA.col2=TC.col2 AND TC.col2=TD.col2`. However, because the optimizer cannot deduce `TA.col2=TD.col2` from the conditions `TA.col2=TC.col2` and `TC.col2=TD.col2`, it is necessary to modify the SQL statement.