Explain Database Indexes and Why Every Column Should Not Be Indexed

Explain what a database index does and why creating an index on every column can make a database perform worse. Use a relational table with frequent inserts and a mix of selective lookups and broad scans as your example. ### Constraints Treat a B-tree index as the main example. Distinguish the logical purpose of an index from engine-specific storage details. No particular table size, schema, or database engine is prescribed; state any example assumptions. ### Clarifying Questions - Which predicates, joins, ordering requirements, and write operations dominate the workload? - How selective are the queried columns, and which queries need several columns together? ```hint Consider both reads and writes An index is another maintained structure. Ask how much work a query avoids and how much additional work a write creates. ``` ### What a Strong Answer Covers - Key-to-row lookup, ordered traversal, and cases in which a scan is cheaper. - Write amplification, storage, cache pressure, and the effect of selectivity. - Composite index ordering, workload evidence, and query-plan validation. ### Follow-up Questions - When can a low-cardinality column still be useful in an index? - How would you detect an unused index before removing it?