Index Design Specifications

Updated on 2025-02-27 GMT+08:00

View PDF

[Specification] Use the index types in the recommended database index practices.

NOTE:

Recommended types must be used for index design. If you need to use prohibited, unrecommended, or restricted index types, contact database experts for advice.

**Table 1** Recommended database index practices
Index Type	Description	Recommended or Not
Primary key or unique index	Single-column or multi-column primary key or unique index.	Recommended
Global index	Index organization method.	Recommended for some data types
Expression index	An index column is a function or scalar expression calculated from one or multiple columns in a table.	Restricted
B-tree index	Index building type.	Recommended for some data types

[Rule] Properly design composite indexes to avoid redundancy.
For example, if an index has been created for (a, b, c), you shall not create an index for (a), (b), (c), (a, b), or (b, c) independently. If only the filtering condition of the a column is used for a query, composite indexes are used for the query as well.

[Rule] A composite index contains a maximum of 5 columns.
[Rule] A composite index contains a maximum of 200 characters.
[Rule] Index (including single-column and composite indexes) columns must be NOT NULL.
[Rule] Do not create multiple unique indexes for a single table.
Maintaining multiple unique indexes at the same time generates more overheads than maintaining a multi-column unique index. If multiple unique indexes are equivalent to a multi-column unique index in service logic, a multi-column unique index shall be preferred.
[Recommendation] The efficiency of maintaining indexes created for the same column is different. Columns of the number type are better than those of the character type and other data types. Therefore, it is recommended that columns such as IDs and time for creating indexes be stored as data of the number type.
[Recommendation] Create indexes on joined columns.
Hash join is supported, whereas nested loop join may be used for join operations if the rescan cost is low (for example, the inner table is small). If the NESTLOOP JOIN plan can be viewed by executing EXPLAIN, you can create indexes on joined columns to improve the efficiency of executing NESTLOOP JOIN.