Explain What a Database Index Is: Lookups, Composite Keys and Write Trade-offs

In a short screening call for a senior, customer-facing engineering role, the interviewer asks: > Explain what a database index is. The call focuses on background, communication and role fit, so the explanation should be correct, well organized and easy to follow for a listener who may not be a database specialist. Expect follow-up questions that go deeper. ```hint Lead with one sentence Before any example or any specific database, say in one sentence what an index is and what problem it solves. ``` ```hint Nothing is free An index speeds up some reads. Think about what it costs on every insert, update and delete, and in storage. ``` ### Clarifying Questions - Does the interviewer want a conceptual explanation, or the internals of a particular database engine? - Should the answer stay with relational databases, or also cover NoSQL stores the candidate has used? ### What a Strong Answer Covers - A plain one-sentence definition, and the problem an index solves compared with scanning the whole table - The underlying structure (such as a B-tree or a hash table) and which kinds of queries each supports - Primary-key versus secondary indexes, and composite indexes where column order matters - The costs: slower writes, extra storage and memory, maintenance, and cases where the index is not used - A concrete example, delivered in a clear order: definition, example, trade-offs ### Follow-up Questions - An `orders` table is often queried by customer number and order number together, and sometimes by customer number alone. Which index would you create, and does column order matter? - Why might the database ignore an index you created and scan the whole table anyway? - How does indexing differ in a wide-column store such as Cassandra, where the primary key also decides how data is partitioned? - A table takes heavy write traffic. How do you decide how many indexes it can afford?