# `IndexFullScan` Operator

+ **`IndexFullScan`** scans the entire index
+ Combined with **`keep order`** behavior and **`Limit`** operator, **`IndexFullScan`** can provide a shortcut
+ Following are two examples, the first one is a shortcut **`IndexFullScan`**

```
tidb> EXPLAIN SELECT MIN(first_name) FROM employees;
+-----------------------+---------+-----------+---------------------------------------------------+-----------------------------------------------------+
| id                    | estRows | task      | access object                                     | operator info                                       |
+-----------------------+---------+-----------+---------------------------------------------------+-----------------------------------------------------+
| StreamAgg_9           | 1.00    | root      |                                                   | funcs:min(employees.employees.first_name)->Column#7 |
| └─Limit_13            | 1.00    | root      |                                                   | offset:0, count:1                                   |
|  └─IndexReader_21     | 1.00    | root      |                                                   | index:Limit_20                                      |
|   └─~!@Limit_20          | 1.00@!~    | cop[tikv] |                                                   | offset:0, count:1                                   |      
|    └─IndexFullScan_19 | 1.00    | cop[tikv] | table:employees, index:idx_first_name(first_name) | ~!@keep order:true@!~                                     |
+-----------------------+---------+-----------+---------------------------------------------------+-----------------------------------------------------+
5 rows in set (0.00 sec)

tidb> EXPLAIN SELECT COUNT(first_name) FROM employees;
+----------------------+-----------+-----------+---------------------------------------------------+-------------------------------------------------------+
| id                   | estRows   | task      | access object                                     | operator info                                         |
+----------------------+-----------+-----------+---------------------------------------------------+-------------------------------------------------------+
| HashAgg_12           | 1.00      | root      |                                                   | funcs:count(Column#8)->Column#7                       |
| └─IndexReader_13     | 1.00      | root      |                                                   | index:HashAgg_5                                       |
|  └─HashAgg_5         | 1.00      | cop[tikv] |                                                   | funcs:count(employees.employees.first_name)->Column#8 |
|   └─IndexFullScan_11 | 300024.00 | cop[tikv] | table:employees, index:idx_first_name(first_name) | ~!@keep order:false@!~                                      |
+----------------------+-----------+-----------+---------------------------------------------------+-------------------------------------------------------+
4 rows in set (0.00 sec)
```

------------------------------------------------------------------------------
At first glance, the two SQL statements on the slide might seem inefficient due to the lack of `WHERE` conditions, which suggests that indexes might not be used. However, both SQL statements can still utilize indexes, and their performance may not necessarily be poor, especially the first query.

Examining the execution plan of the first SQL statement reveals that the initial child step is `IndexFullScan`. This operator performs a full scan of the entire index on the TiKV nodes. While not the most efficient method, it is still preferable to scanning the entire table due to the reduced number of columns in the index. The `SELECT` clause of the SQL statement only requires the minimum value of the `first_name` column, which is sorted in ascending order by the `idx_first_name` index. The `IndexFullScan` operation only needs to scan one index entry in ascending order. Consequently, the second-to-last operation in the execution plan is the `Limit` operator. This operator efficiently retrieves the top values from the ordered scan, making it more efficient than scanning all rows and surpassing the `IndexLookUp` operation, which involves additional table lookups. To give an example, from the perspective of function calls, the `Limit` function invokes the `IndexFullScan` function and bypasses the unneeded loops.

To summarize the execution process of the first SQL statement: The `IndexFullScan` in the fifth row performs a full scan on the `idx_first_name` index. Since only the minimum value is needed, the `Limit` operator in the fourth row restricts the scan to just the first row. The scanned index values are then sent to the TiDB Server, which retrieves the minimum `first_name` value. This is the `IndexReader` operation, indicating that the entire process is completed by reading the index without requiring a table lookup. The `StreamAgg` data aggregator, shown in the first row of the execution plan, will be discussed in detail in the section on optimizing table aggregation.

Compared to the first SQL statement, the second SQL statement is less efficient because it requires counting non-null values in the `first_name` column, necessitating a full scan of the `idx_first_name` index. The process is as follows: Starting with the `IndexFullScan` in the fourth row, a full scan of the `idx_first_name` index is performed. The `HashAgg` operations in the third and first rows handle aggregation. In the second row, the `IndexReader` retrieves data from the TiKV nodes based solely on the `idx_first_name` index, avoiding a table lookup.

Examining the execution plan reveals that the performance is promising when `IndexFullScan`, `keep order:true`, and `Limit` are used together.