Database Indexing Explained

Q: What is an index in a database and how does it help? A: An index is an auxiliary data structure (commonly a B-Tree or B+Tree, sometimes a hash index) built on one or more columns that lets the database locate rows matching a WHERE/JOIN/ORDER BY condition without scanning the entire table. It trades additional storage and slower writes (every insert/update/delete must also update the index) for dramatically faster reads - turning an O(n) full table scan into an O(log n) lookup. Composite indexes (multiple columns) help multi-column filters but only when the leftmost columns are used in the query (leftmost-prefix rule). Over-indexing hurts write-heavy workloads, so indexes should be chosen based on actual query patterns (frequently filtered/sorted/joined columns).