Selecting the appropriate index type in 4D is essential for optimizing data access and ensuring efficient performance. 4D provides three index options that are chosen based on the field’s selectivity, which is defined as:

Selectivity = (Distinct Values) / (Total Number of Rows)

B-Tree Index

• B-Tree Index: Best suited for fields with high selectivity, where the number of distinct values is close to the total number of rows.
• Common examples include primary keys or UUIDs that have many unique values.

Cluster B-Tree Index

• Cluster B-Tree Index: Best for fields with low selectivity, meaning the number of distinct values is very low compared to the total number of rows.
• This index type organizes the data physically in the order of the indexed field, which can improve performance for specific queries.
• A good example is a field with status values (e.g., a field that only contains a few distinct values), where even a table with millions of records might only have a few unique statuses.

Automatic Index
Automatic: This setting lets 4D choose between the other two index types—B-Tree and Cluster B-Tree—based on the selectivity of the field.