Junior From PDF SQL SQL & Databases

What is a covering index?

Q: What is a covering index?

Short answer: A covering index is an index that contains all the columns needed by a query, meaning the query can be answered entirely by the index without accessing the actual table data. This can improve query performance by reducing disk I/O. Example code If a query selects id, name, and salary from the employees table and there is an index on (id, name, salary), the index itself is sufficient to satisfy the query, and the database doesn't need to access the table at all. Real-world example (ShopNest) ShopNest adds an index on Orders(CustomerId, CreatedAt) because “my recent orders” is queried constantly. Say this in the interview Define — one clear sentence (the short answer above). Example — relate it to a project like ShopNest or your real work. Trade-off — when you would not use it.

Short answer: A covering index is an index that contains all the columns needed by a query, meaning the query can be answered entirely by the index without accessing the actual table data. This can improve query performance by reducing disk I/O.

Example code

If a query selects id, name, and salary from the employees table and there is an index on (id, name, salary), the index itself is sufficient to satisfy the query, and the database doesn't need to access the table at all.

Real-world example (ShopNest)

ShopNest adds an index on Orders(CustomerId, CreatedAt) because “my recent orders” is queried constantly.

Say this in the interview

Define — one clear sentence (the short answer above).
Example — relate it to a project like ShopNest or your real work.
Trade-off — when you would not use it.

Previous Next