5.3.2 Points to consider in determining the columns to be defined for a B-tree index
HADB uses a write-once updating method. Therefore, updating a column value updates the entire row (the existing row becomes invalid and a new row is inserted). When a B-tree index is defined for a table, updating a row also updates the B-tree index key value. If more B-tree indexes than necessary are defined for a table whose rows are frequently updated, the performance of update processing deteriorates accordingly.
When choosing the columns on which B-tree indexes will be defined, consider the points described in the following subsections.
- Organization of this subsection
(1) Columns that benefit from being defined for a B-tree index
The following columns benefit if B-tree indexes are defined on them:
- Columns to be specified in the conditions used to narrow down data
-
When you perform a search with a search condition specified in a WHERE clause, the number of pages that must be searched can be reduced (narrowing down the search range) if you define a B-tree index on the column specified in the search condition. Since this improves search performance, consider defining a B-tree index on those columns specified in the search condition that can narrow down the data search range.
- Columns to be specified in the conditions used to join tables (for searching across multiple tables)
-
When the search target data is distributed across multiple tables and columns having common information are mapped to each other when searching (by joining the tables), you can reduce the number of times the join condition must be evaluated (the number of times rows must be matched) if you define a B-tree index on the column specified in the join condition. Since this improves the search performance, consider defining a B-tree index on the column to be specified in the join condition.
If all of the following conditions are met, consider defining also a range index on the column to be specified in the join condition:
-
The table for which a B-tree index is to be defined is a multi-chunk table.
-
The column to be specified in the join condition applies to any of the cases shown in (1) Cases that benefit from a range index in 5.5.1 Points to consider when defining a range index.
-
(2) Columns that benefit from not being defined for a B-tree index
The following columns benefit if B-tree indexes are not defined on them:
- Columns that contain a lot of redundant data
-
Even if you define a B-tree index on columns that contain a lot of redundant data, you cannot narrow down the data search range. Therefore, this does not reduce the number of pages that must be searched. To avoid search performance degradation, it is best not to define for a B-tree index columns that contain a large amount of redundant data.