global index limitation: Unsupported Global Index including all columns in the partitioning expression

[dbsid]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

We are encountering hotspots on both the AUTO_INCREMENT primary key and the updated_at index during ingestion. To address this, I plan to implement hash/key partitioning to alleviate the hotspots on both the primary key and the secondary index (idx_updated_at). The specific actions I intend to take are:

1. Keep the primary key and idx_updated_at as local indexes to distribute load and mitigate the hotspots.
2. Convert the index on user_id (idx_user_id) into a global index. Since user_id is not unique, I will modify the index by including the id column, ensuring idx_user_id becomes a unique index.

# original schema
CREATE table t (
  id bigint NOT NULL AUTO_INCREMENT,
  updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  user_id bigint DEFAULT NULL,
  PRIMARY KEY (id),
  KEY idx_user_id (user_id, id),
  KEY idx_updated_at (updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

set session tidb_enable_global_index=on;

CREATE table t (
  id bigint NOT NULL AUTO_INCREMENT,
  updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  user_id bigint DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY idx_user_id (user_id, id) global,
  KEY idx_updated_at (updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY KEY(id)
PARTITIONS 64;

2. What did you expect to see? (Required)

no error

3. What did you see instead (Required)

mysql> CREATE table t (
    ->   id bigint NOT NULL AUTO_INCREMENT,
    ->   updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->   user_id bigint DEFAULT NULL,
    ->   PRIMARY KEY (id),
    ->   UNIQUE KEY idx_user_id (user_id, id) global,
    ->   KEY idx_updated_at (updated_at)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    -> PARTITION BY KEY(id)
    -> PARTITIONS 64;
ERROR 8200 (HY000): Unsupported Global Index including all columns in the partitioning expression

4. What is your TiDB version? (Required)

v8.3.0

[dbsid]

/assign @mjonss