Does the _tidb_rowid column have an index?

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

Original topic: _tidb_rowid 列有索引吗

| username: xingzhenxiang

When backing up, I found the following statement and would like to ask if _tidb_rowid has an index:

SELECT * FROM dbname.w_packet WHERE create_date_time > ‘2022-01-01 00:00:00’ ORDER BY _tidb_rowid

| username: tidb菜鸟一只 | Original post link

Please provide the structure of the w_packet table.

| username: xingzhenxiang | Original post link

This _tidb_rowid is an internal TiDB field. The table information is as follows:

CREATE TABLE w_packet (
id char(64) NOT NULL COMMENT ‘Primary Key ID’,
status varchar(10) NOT NULL COMMENT ‘Status’,
create_date_time datetime NOT NULL COMMENT ‘Creation Time’,
complete_date_time datetime DEFAULT NULL COMMENT ‘Completion Time’,
final_date_time datetime DEFAULT NULL COMMENT ‘Final Completion Time’,
serial_number varchar(64) DEFAULT NULL COMMENT ‘Serial Number’,
is_delete varchar(10) DEFAULT ‘0’ COMMENT ‘Is Deleted’,
summary varchar(256) DEFAULT NULL COMMENT ‘Summary’,
sys_update_data_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘System Update Time’,
PRIMARY KEY (id),
KEY idx_w_packet_create_date_time (create_date_time),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

| username: TiDBer_jYQINSnf | Original post link

In the case of a non-clustered table without an integer primary key, the hidden column _tidb_rowid is the primary key, which means it is the index.

| username: tidb菜鸟一只 | Original post link

Yes, when you create a table without a primary key or with a non-numeric primary key, this field will be generated. This field is similar to Oracle’s rowid and corresponds to the key on TiKV. A corresponding primary key index will also be created on it.

| username: xingzhenxiang | Original post link

Thank you for your reply.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.