Log In    |    Knowledge Base    |    4D Home
Tech Tip: When to use B-Tree Indexes versus Cluster B-Tree Indexes
PRODUCT: 4D Developer | VERSION: 11 | PLATFORM: Mac & Win
Published On: July 30, 2008

B-Tree indexes store each unique record separately as a unique key while Cluster B-Tree indexes have a unique key only for each distinct value in the table. In Cluster B-Tree indexes each unique key points to a bit table or array (decided on by 4D for memory purposes) which lists the records in the table which match that key.

The decision of which index to use is based on a field's selectivity. Selectivity is a rating derived from the number of distinct values in a field divided by the total number of records. The equation follows:

Selectivity = (Number of distinct values in the field) / (Total number of records in the tables)

Queries on fields with higher selectivity (closer to the maximum value of 1) are more efficient when using a B-Tree index. This is because there would be a large number of keys in a Cluster B-Tree index in this case to be selected from, while in a B-Tree a tree walk would take place to find the correct keys. When the selectivity is low though and the number of keys in the Cluster B-Tree index is low then the selection is fast and it is very fast to find a large number of records. In this case a B-Tree index would need to walk the tree many times to find all the matching records, which would lead to slower query performance.