Does a unique index require a table lookup?

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 唯一索引需要回表吗

| username: Trouble

I am a beginner and have some questions about unique indexes.
As shown in the picture:

id is the primary key, age is the unique key; at this time, if age is used as the filter condition, does the kernel need to go back to the table?
My understanding is: the projection fields are all, and the unique key cannot obtain all the data, so TiDBserver needs to first go to TiKV to get the rowid corresponding to the unique key. After TiDBserver gets the rowid, it then goes to TiKV to get all the data.
Is this understanding correct?
If this is the case, why is the execution plan Point_Get? Does Point_Get mean there is no need to go back to the table?

| username: Billmay表妹 | Original post link

If id is the primary key and age is a unique key, and you use age as a filter condition, TiDB’s execution plan might use Point_Get.

Point_Get is a query optimization strategy that can directly retrieve the required data from the index without performing a table lookup. In this case, TiDB might use the unique key index of age to locate the row that meets the condition and directly retrieve the required data from the index without performing a table lookup.

Specifically, TiDB might execute the query using the following steps:

  1. Locate the index position of the row that meets the condition based on the unique key index of age.
  2. Retrieve the required data from the index, including the primary key id and other projection fields.
  3. Return the query result without performing a table lookup.

It should be noted that the specific choice of the execution plan depends on multiple factors, including the structure of the table, the choice of indexes, data distribution, etc. Therefore, the actual execution plan may vary depending on the specific situation.

For more information on execution plans and query optimization, you can refer to the relevant sections in the official documentation, such as “Execution Plan” and “Query Optimization.”

| username: Miracle | Original post link

You can check 聚簇索引 | PingCAP 文档中心
point_get only indicates point query, it has nothing to do with whether to return to the table or not.

| username: 啦啦啦啦啦 | Original post link

A table lookup is required, and the where condition is not a unique index field, but it could also be a point_get.

| username: Trouble | Original post link

Okay, thank you.

| username: Trouble | Original post link

Got it, thanks.

| username: xingzhenxiang | Original post link

Back to the table.

| username: 裤衩儿飞上天 | Original post link

You need to perform a table lookup for this.

If the index covers the columns to be queried, then a table lookup is not needed.

| username: Trouble | Original post link

What about the primary key? If the primary key is used as a filter condition, it shouldn’t require a table lookup, right?

| username: Trouble | Original post link

If the primary key is also the rowid, then there’s no need to go back to the table, right?

| username: 裤衩儿飞上天 | Original post link

Clustered indexes (which can be understood as the primary key being the rowid) do not require a table lookup, while non-clustered indexes (where the rowid is not the primary key) still require a table lookup.

If the index covers the query columns, a table lookup is not needed.

| username: Trouble | Original post link

OK, thank you very much.
If the primary key is an integer type, it is also the rowid, and using it as a filter condition does not require a table lookup.
If the primary key is of another data type (cannot be used as rowid), similar to a unique index, if it is not a covering index query when used as a filter condition, a table lookup is also required.

| username: 裤衩儿飞上天 | Original post link

You can take a look at the sections related to clustered indexes and non-clustered indexes.

| username: forever | Original post link

No, what is being said is that if the primary key unique index is used, if it is a clustered index, then there is no need to go back to the table. For non-clustered index primary keys, you still need to go back to the table. For non-primary key unique indexes, you need to go back to the table for any table (except when the index column includes all columns, which would result in index coverage and no need to go back to the table).

| username: Trouble | Original post link

Got it, I’ll go check the clustered index section.
Another question:
If I update the index field, is it possible for this key to be transferred from one region to another?
If so, does that mean the routing information in PD and Region Cache will be outdated?

| username: forever | Original post link

The information in PD will not expire. PD records the range of regions. It’s just that your key is no longer in the current region and is now stored in a new region. PD’s information still records the current region as this range, and your new value will be in the region information of another PD.

| username: Trouble | Original post link

Wow :rofl:
I was really dumb, my brain didn’t catch up.

| username: xingzhenxiang | Original post link

The primary key depends on whether it is a clustered table. Non-clustered tables require a table lookup. This is also why I said in the group that “select name from t where name=‘lisi’” will definitely not require a table lookup, but I didn’t say “select name, id from t where name=‘lisi’” will definitely not require a table lookup.

| username: Trouble | Original post link

Got it, thanks :+1:

| username: Jiawei | Original post link

I think pointget emphasizes more on returning one row of data, one record, and it is not directly and strongly related to whether it goes back to the table or not.