Index Theory

Database indexes are deep. Sometimes bottomlessly so.

B-tree is the classic. Most RDBMSes use it by default. A balanced tree structure that handles both range scans and equality lookups. A generalist — and generalists are never optimal everywhere.

Bitmap indexes are a different animal. They shine on low-cardinality columns — gender, prefecture, status flags. Bitwise AND/OR operations run fast. Perfect for OLAP. Terrible for write-heavy OLTP. Lock granularity is coarse. Concurrent writes kill you.

Oracle held a patent on bitmap indexes for years. That is one reason PostgreSQL and MySQL never shipped the same implementation. Not a technical limitation. A legal one. Implementation differences across databases cannot be explained by algorithms alone.

Function-based indexes were another Oracle-first feature. You could index an expression like UPPER(name). PostgreSQL followed later. MySQL held out for years and offered Generated Columns as a detour instead. Same problem, completely different approach.

Inverted indexes belong to the world of full-text search. This is the territory where I built MygramDB — because MySQL's FULLTEXT index was painfully slow. Trying to do full-text search with a B-tree is like eating soup with a fork. Wrong tool.

Index selection is always a tradeoff. Read speed, write cost, storage footprint, lock granularity. What you prioritize changes the best answer. And which RDBMS you run changes the options themselves. Same table, different cutlery.