Katkat
Back

Explain Database Indexes and Why Every Column Should Not Be Indexed

SquarepointSoftware Engineer · Onsite
EasySoftware EngineerOnsiteSoftware Engineering Fundamentals

Last updated

Explain database indexes, selective lookups, write overhead, composite keys, and why indexing every column can hurt a mixed workload.

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?

Hints

Hint 1

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.

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?