# Overview A database index is an additional data structure a database will create to more efficiently find the data it needs. Rather than constantly moving randomly across the physical hardware (e.g., [[Solid State Drive (SSD)]], [[Hard Disk Drive (HDD)]]) of the data, the indexing data structure will provide a means of quickly finding the data (the method used to find the data differs depending on the *type* of index). The end result of an index is it will provide much faster reads, but slower writes. The reason writes are slower is the index becomes overhead that must be maintained each time new data enters the system (i.e., the index needs to be recalculated). # Key Considerations When determining what database indexes to use, or if they are necessary, you should consider: - [[Data Access Patterns]] - what are the most common queries? How will data be accessed? - Data Size - large table benefit from indexing, but the indexes also take more space - Write Volume - if you have heavy writes, then indexes will reduce throughput - [[Cardinality]] - how unique are the values? Low cardinality columns may not benefit from indexing ## Types of Database Indexes | Type | Description | Pros | Cons | Use Cases | | ------------------------------------------ | ---------------------------------------------------- | ------------------------------------------------ | ------------------------------------------------ | ----------------------------------------------------- | | [[Hash Index]] | ![[Hash Index#Overview]] | ![[Hash Index#Pros]] | ![[Hash Index#Cons]] | ![[Hash Index#Use Cases]] | | [[B+ Tree Index]] | ![[B+ Tree Index#Overview]] | ![[B+ Tree Index#Pros]] | ![[B+ Tree Index#Cons]] | ![[B+ Tree Index#Use Cases]] | | [[Inverted Index]] | ![[Inverted Index#Overview]] | ![[Inverted Index#Pros]] | ![[Inverted Index#Cons]] | ![[Inverted Index#Use Cases]] | | [[Log Structured Merge (LSM) Trees Index]] | ![[Log Structured Merge (LSM) Trees Index#Overview]] | ![[Log Structured Merge (LSM) Trees Index#Pros]] | ![[Log Structured Merge (LSM) Trees Index#Cons]] | ![[Log Structured Merge (LSM) Trees Index#Use Cases]] | | [[B-Tree Index]] | ![[B-Tree Index#Overview]] | ![[B-Tree Index#Pros]] | ![[B-Tree Index#Cons]] | ![[B-Tree Index#Use Cases]] | | [[R-trees Index]] | ![[R-trees Index#Overview]] | ![[R-trees Index#Pros]] | ![[R-trees Index#Cons]] | ![[R-trees Index#Use]] | | [[Geospatial Indexes]] | ![[Geospatial Indexes#Overview]] | ![[Geospatial Indexes#Pros]] | ![[Geospatial Indexes#Cons]] | ![[Geospatial Indexes#Use Cases]] | ## Indexing Strategies - [[Clustered Index]] - [[Composite Index]] - [[Partial Index]] - [[Covering Index]] # Pros - Improves read performance # Cons - Negatively impact write performance, because they need to be updated as new data enters the database # Use Cases # Related Topics