PolarDB:Notes for using GSIs

Notes for creating a GSI

• You cannot create a GSI on ApsaraDB RDS for MySQL 5.6.

• You cannot create a GSI in a single table or broadcast tables.

• You cannot create a GSI in tables that do not have primary keys.

• You cannot use a prefixed index in a UNIQUE GSI.

• Specify the index name when you create an index table.

• When you create an index table, specify database sharding rules or database sharding and table sharding rules. You cannot specify only table sharding rules or specify no sharding rules.

• The INDEX column in index tables must contain all shard keys.

• The name of a GSI cannot be the same as that of other local indexes in a primary table.

• Index columns and covering columns cannot be the same in GSI definition clauses.

• By default, an index table contains all the primary keys and shard keys of the primary table. If the primary keys and shard keys are not explicitly contained in the index columns, the keys are added to the covering columns by default.

• For each local index in the primary table, if all the referenced columns are contained in the index table, the local index is added to the index table by default.

• By default, an index is separately created for each index column of a GSI if no index exists.

• By default, a composite local index is created for a GSI that contains multiple index columns. The composite local index contains all the index columns.

• In an index definition, the length parameter of the index column is used only to create local indexes on the shard keys of the index table.

• If you create a GSI after a table is created, data is automatically verified at the end of the creation process. The execution of the data definition language (DDL) statement to create the GSI can be successful only after the data passes the verification.

  Note

  You can also use the CHECK GLOBAL INDEX statement to verify or modify the index data.

Notes for executing the ALTER TABLE statement

• The following table describes whether the ALTER TABLE statement can be executed to change columns.

  Statement	Change the shard keys of the primary table	Change the primary keys of the primary table, also referred to as the primary keys of the index table	Change the local unique index column	Change the shard keys of the index table	Change the unique index column	Change the index column	Change the covering column
  ADD COLUMN	No such scenario	Not supported	No such scenario	No such scenario	No such scenario	No such scenario	No such scenario
  ALTER COLUMN SET DEFAULT and ALTER COLUMN DROP DEFAULT	Not supported	Not supported	Supported	Not supported	Not supported	Not supported	Not supported
  CHANGE COLUMN	Not supported	Not supported	Supported	Not supported	Not supported	Not supported	Not supported
  DROP COLUMN	Not supported	Not supported	Supported only when the unique key has only one column	Not supported	Not supported	Not supported	Not supported
  MODIFY COLUMN	Not supported	Not supported	Supported	Not supported	Not supported	Not supported	Not supported

  Note

  • To ensure the stability and performance of GSIs, you are not allowed to delete columns from GSIs by executing the DROP COLUMN statement in a direct way. To delete some columns from a GSI,you can execute the DROP INDEX statement to delete the GSI and create another GSI.

  • Overlaps exist in the preceding column classification. For example, the index column contains the shard keys of the index table. The covering column contains the shard keys of the primary table, the primary keys, and the specified columns. If a conflict occurs between Not supported and Supported, Not supported takes precedence over Supported.

• The following table describes whether the ALTER TABLE statement can be executed to change indexes.

  Statement	Supported or not
  ALTER TABLE ADD PRIMARY KEY	Supported
  ALTER TABLE ADD [UNIQUE/FULLTEXT/SPATIAL/FOREIGN] KEY	Supported. You can add local indexes to the primary table and the index table at the same time. An index name cannot be the same as the name of the GSI.
  ALTER TABLE ALTER INDEX index_name {VISIBLE | INVISIBLE}	Supported. You can execute this statement on only the primary table. You are not allowed to change the GSI status.
  ALTER TABLE {DISABLE | ENABLE} KEYS	Supported. You can execute this statement on only the primary table. You are not allowed to change the GSI status.
  ALTER TABLE DROP PRIMARY KEY	Not supported
  ALTER TABLE DROP INDEX	You can delete only a common index or a GSI.
  ALTER TABLE RENAME INDEX	Not supported

  Note

  To ensure the stability and performance of GSIs, you are not allowed to rename GSIs by executing the ALTER TABLE RENAME INDEX statement in a direct way. To change the GSI name, you can execute the DROP INDEX statement to delete the GSI and create another GSI by using a new name.

Notes for changing index tables

• You cannot execute DDL or data manipulation language (DML) statements on index tables.

• You cannot execute DML statements that contain NODE HINT to update primary tables or index tables.

Notes for executing other DDL statements

Notes for executing DML statements

• You cannot execute DML statements on index tables.

• For more information about the limits on executing DML statements on primary tables, see Limits of global secondary indexes on DML.