# Using Indexes to Satisfy `ORDER BY` Clause - Cont'd

+ The attribute **`keep order`** in the **`operator info`** column indicates the usage of indexes
+ **`true`** indicates a sort operation is skipped
+ **`false`** indicates a sort operation is necessary to satisfy the **`ORDER BY`** clause

![`smart`](UseIndexes2SatisfyOrderBy02_en_001_20230129.png)

------------------------------------------------------------------------------
From the slides, we can see that there is no `Sort` operator in the SQL query, and the sorting is done through the ordering of the index `(hire_date, gender, last_name)`. To design an index that avoids sorting, consider the following:

1. In a multi-column index, the columns before the sorting column must appear in the `WHERE` clause. For example, the following multi-column indexes cannot avoid sorting in the TiDB Server's memory:

```
(gender, birth_date, first_name, last_name)
(first_name, last_name)
```

On the contrary, the following multi-column indexes can avoid sorting in the TiDB Server's memory

```
(gender, birth_date, last_name)
(birth_date, last_name)
(gender, last_name)
(last_name)
```

2. In a multi-column index, if the columns before the sorting column appear in the `WHERE` clause, they must be an equal query. For example, the following `WHERE` clauses cannot use the index `(gender, hire_date, last_name)` or `(hire_date, last_name)` to avoid sorting in the TiDB Server's memory:

```
WHERE birth_date BETWEEN '1955-01-01' AND '1955-02-01' AND gender = 'M' ORDER BY last_name;
```

3. If there are multiple columns in the `ORDER BY` clause, the order of the sorting columns in the multi-column index must correspond to the columns in the `ORDER BY` clause. For example:

```
WHERE birth_date = '1955-01-01' AND gender = 'M'  ORDER BY last_name, first_name, hire_date;
```

In summary, the sorting column can be designed as `(gender, birth_date, last_name, first_name, hire_date)`, `(gender, birth_date, last_name, first_name)`, or `(gender, birth_date, last_name)`. The indexes `(gender, birth_date, last_name, first_name)` and `(gender, birth_date, last_name)` help reduce the sorting operation in the TiDB Server’s memory. In contrast, indexes such as `(gender, birth_date, first_name, hire_date)`, `(gender, birth_date, first_name)`, or `(gender, birth_date, hire_date)` do not alleviate any sorting operations.