# Working with Indexes - Case Study

![`smart`](OptimizerHints01_en_001_20240207.png)

----------------------------------------------------
We execute the following SQL query:

```
SELECT col5, col6 FROM T1 WHERE col2 = 5 AND col1 = 1000 ORDER BY col3 DESC LIMIT 1;
```

Subsequently, we navigate to the `SQL statement` function in the `TiDB Dashboard` and locate the recently executed query. Upon examining its `EXPLAIN ANALYZE` output, we observe that the optimizer employed the `IndexFullScan` operator. As discussed in our previous course, the `IndexFullScan` operator is not highly efficient because its performance declines as the table's data volume increases. Furthermore, we note that the `estRows` column shows a value of 500 rows, while the `actRows` column indicates that 100,000 rows were actually scanned. This indicates that the `IndexFullScan` operator scanned the entire table, with a significant discrepancy between the optimizer's estimated rows (`estRows`) and the actual rows scanned (`actRows`).

In the SQL query, `LIMIT 1` implies that the expected result is a single row, and `WHERE col2 = 5 AND col1 = 1000` suggests a selectivity of 1/5 x 1/100 = 1/500 for the result set. Here, 1/100 represents the selectivity of col2, and 1/5 indicates the selectivity of col1. The calculation method for the `estRows` column in the SQL query is: expected number of rows returned / selectivity of the result set. Therefore, we have 1 / (1/5 x 1/100) = 500 rows. Since the query includes `ORDER BY col3` and an index exists on the `col3` column, the result set can remain sorted. Based on these calculations, the optimizer opted for the `IndexFullScan` operator. However, due to the data distribution, scanning all rows is necessary to obtain the desired result.

At this point, we can employ an `Optimizer Hint` to improve the SQL query's performance!