How Indexing Works
Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table.
B-Tree Indexes
Most relational databases (like MySQL and PostgreSQL) use B-Trees (Balanced Trees) for indexing. They keep data sorted and allow searches, sequential access, insertions, and deletions in logarithmic time (O(log N)).
Types of Indexes
- Primary Index: Created automatically on the primary key.
- Clustered Index: Alters the physical order of the table. A table can have only one clustered index.
- Non-Clustered Index: Does not alter physical order; creates a separate logical structure.
