4. Indexes
Duplicate columns should not be included in an index
Code: DuplicateColumnsInIndex
Rule Description: Including duplicate columns in an index is unnecessary as it does not improve query performance and may lead to reduced index efficiency.
Warning Level: Warning
Review Object: CREATE INDEX, ALTER TABLE ADD INDEX
Trigger Condition: The index contains duplicate columns.
Configurable: No
Supported Database Types: All
Number of fields in an index should not exceed the threshold
English Name: NumberOfColumnsInIndexExceedThreshold
Rule Description: Limiting the number of fields in an index to avoid excessively large indexes and performance issues.
Warning Level: Warning
Review Object: CREATE INDEX, ALTER TABLE ADD INDEX
Trigger Condition: The number of fields in the index exceeds the preset threshold (e.g., 5).
Configurable: Yes
Example: The expression can be configured as 5, indicating that the number of fields in an index should not exceed 5.
Supported Database Types: All
TEXT and LOB type fields should not be included in indexes
English Name: Text&LobColumnsInIndexDisallowed
Rule Description: TEXT and LOB type fields should not be included in indexes, as these types of fields are usually large and can lead to poor index performance.
Warning Level: Warning
Review Object: CREATE INDEX, ALTER TABLE ADD INDEX
Trigger Condition: The index includes fields of type TEXT or LOB.
Configurable: No
Supported Database Types: All
Nullable columns should be avoided in indexes
English Name: AvoidUsingNullableColumnsInIndex
Rule Description: It is recommended to avoid using nullable columns in indexes, as nullable columns may lead to incomplete indexes and affect query performance.
Warning Level: Warning
Review Object: CREATE INDEX, ALTER TABLE ADD INDEX
Trigger Condition: The index includes nullable columns.
Configurable: No
Supported Database Types: All
Index fields should have a discrimination rate higher than the threshold
English Name: IndexFilterFactorShouldLowerThanThreshold
Rule Description: The selection of index fields should be based on the discrimination rate of the fields, i.e., the uniqueness of the distribution of field values, to ensure the effectiveness of the index.
Warning Level: Notice
Review Object: CREATE INDEX, ALTER TABLE ADD INDEX
Trigger Condition: The discrimination rate of the index fields is below the preset threshold (e.g., 0.1).
Configurable: Yes
Example: The expression can be configured as 0.1, indicating that the discrimination rate of index fields should be higher than 10%.
Supported Database Types: All
Global indexes on partitioned tables are prohibited
English Name: GlobalIndexOnPartitionedTableDisallowed
Rule Description: Global indexes on partitioned tables are prohibited as they may lead to performance issues and increased management complexity.
Warning Level: Warning
Review Object: CREATE INDEX, ALTER TABLE ADD INDEX
Trigger Condition: A global index is created on a partitioned table.
Configurable: No
Supported Database Types: openGauss
Prohibited creation of duplicate/redundant indexes
English Name: RedundantIndexDisallowed
Rule Description: The creation of duplicate or redundant indexes is prohibited, as it wastes storage space and reduces write performance.
Warning Level: Warning
Review Object: CREATE INDEX, ALTER TABLE ADD INDEX
Trigger Condition: A duplicate or redundant index is created compared to existing indexes.
Configurable: No
Supported Database Types: All
Field length for index creation should not exceed the threshold
English Name: LongLengthColumns4IndexDisallowed
Rule Description: Limiting the length of fields in indexes to avoid excessively large indexes and performance issues.
Warning Level: Warning
Review Object: CREATE INDEX, ALTER TABLE ADD INDEX
Trigger Condition: The length of fields used to create an index exceeds the preset threshold (e.g., 32).
Configurable: Yes
Example: The expression can be configured as 32, indicating that the length of fields used for index creation should not exceed 32 characters.
Supported Database Types: All