# Subquery Optimization - Case Study 1

+ Why Index Join, or Index Hash Join, is not used here?
  + To use Index Join, or Index Hash Join, the outer table (`T1`) must be the driving table.
  + Table `T2` is much smaller than table `T1`, which is made driving table by the optimizer

![`smart`](Subquery04_aa_001_20240220.png)

------------------------------------------------------------------------------
From the `SQL Statements` section of the `TiDB Dashboard`, we can observe that the optimizer rewrites the subquery in the SQL statement as a `semi join`, using a `hash join` to execute the join operation. 

However, this approach is inefficient: `HashJoin_19` processes only 10 rows, yet its child operator, `TableReader_26`, reads 10,000 rows. As we've discussed previously, the efficiency of this table join is suboptimal.

Considering that both tables, `T1` and `T2`, have highly selective indexes on the `col1` column, and table `T2` contains significantly less data than `T1`, why doesn't the `semi join` use an `index join` or `index hash join`? The reason is that for a `semi join` to utilize an `index join` or `index hash join`, the outer query must serve as the driving table, while the inner query acts as the driven table. In this case, if the `semi join` were to use an `index join` or `index hash join`, `T1` would need to be the driving table and `T2` the driven table. Therefore, the optimizer still opts for a `hash join` for the `semi join`.

In conclusion, the execution plan selected by the optimizer is not the most efficient one. We can enhance it by applying optimizer hints.