Bookmark items:

https://landing.athabascau.ca/pg/bookmarks/read/127307/how-btree-database-indexes-work-and-how-to-tell-if-they-are-efficient-100-level

https://landing.athabascau.ca/pg/bookmarks/read/127296/memory-database-index-optimization

Performance tuning becomes necessary when the amount of records in the database increases over time. In addition to upgrading the server hardware, one of the most used techniques is to add indexes to commonly filtered columns. Columns that involves in joins (FK), in the where clause and in the order by clause of a query. Primary keys are already indexed by default; foreign keys often benefits from non-unique index due to the likelihood of them being used in a join statement.

 According to the bookmark item #1, b-tree type index is only effective with high cardinality, meaning that the column values should be highly unique for the index to be effective. The reason being is that a b-tree index is essentially a sorted list with binary search. Doing binary search on a field with low cardinality results in a very low “selectivity” value. Binary search doesn’t work well with an array of values with two variants such as a Boolean value.

Bitmap index should be used in low cardinality columns.

Dickson