Index on a low cardinality column

#1

" If many of the values in the field are the same, the index might not significantly speed up queries."

"An index on the gender column likely would not help because there's no value in an index on a low cardinality column. Meaning, there's not enough variety in the values for the index to provide any value."

an index on a low cardinality column is generally not recommended as mentioned above. So is there any difference in ObjectDB?

TIA

#2

Generally that is correct, an index is usually more effective when the values are diverse. However it could still help in certain cases (e.g., a count query, as part of a composite index, etc.), so the recommendation is to try a simple benchmark if in doubt.

ObjectDB Support
#3

I mean ObjectDB maybe has a different way to create an index, which has high performance beyond a relation DB.

#4

Actually ObjectDB is similar to other databases in this case.

There are specific cases that such an index would be useful. For example, an index on a gender field can be helpful in count queries that only return the numbers of males and females. As a rule of thumb ask yourself, does the index contains all the information that is needed for executing the query? If it does it would be helpful. Therefore, composite indexes, including with boolean fields, can be powerful if they cover all the needed data for a query. If the index does not contain all the the necessary data then it can only provide an initial filtering but the actual data has to be accessed. In such cases, the question is how good the index is in filtering. Filtering 50% might be useless, because scanning all the data pages will still be needed anyway.

ObjectDB Support
#5

Oh, I see. The implement of index in ObjectDB is B+tree.

Reply