[Interview Question] What is DB Index?

Data structures to speed up searches of database tables

Advantages of using indexes

• Improved performance due to faster table lookups

• Can reduce system load

Disadvantages of using indexes

• Requires additional storage space for DB indexing

• Additional work and overhead for DB management

Index management

Because indexes must always be kept up-to-date and sorted to quickly find the desired value, hanging an index on a property whose data is frequently modified can result in a performance penalty due to overhead.

• INSERT: Adds an index of the inserted data on insertion

• DELETE: indexes on deleted data should be disabled

• UPDATE: Disable existing indexes + add indexes for updated data

Data structures used

• B+ Tree

B Tree enhancement specification

• Store data only in leaf nodes

• Leaf nodes have a linked list between them

• Hash Table

Stores data as key - value pairs Data can be navigated in a very short amount of time Not often used due to inequality operations (sort X)

Classification of indexes

1. Classification by Key

• Primary index: contains primary key (order of key = order of records)

• Secondary index: without primary key (order of keys != order of records)

2. Classification based on file organization

• Focused index

• Non-centralized indexes

3. Categorize by data scope

• Dense indexes

• Sparse indexes

Note

brunchstory by Nathan