What is a database index and when should you add one?
sql-jun-004
Your answer
Answer as you would in a real interview — explain your thinking, not just the conclusion.
Model answer
An index is a separate data structure — typically a B-tree — that the database maintains alongside the table to allow fast lookups without scanning every row. Think of it as a book's index: instead of reading every page you jump straight to the right page number. You should add an index on columns that appear frequently in WHERE clauses, JOIN conditions, or ORDER BY clauses on large tables. The tradeoff is that every write (INSERT, UPDATE, DELETE) must also update all relevant indexes, so over-indexing hurts write performance and wastes storage. I typically add indexes for foreign key columns, high-cardinality filter columns, and columns used in sorting on frequently-queried tables.
Code example
-- Simple index on a frequently filtered column
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
-- Covering index includes all columns the query needs
-- so the DB never has to hit the main table
CREATE INDEX idx_orders_status_created
ON orders(status, created_at)
INCLUDE (total_amount, customer_id); -- SQL Server syntax
-- Check index usage (PostgreSQL)
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total_amount
FROM orders
WHERE status = 'pending'
ORDER BY created_at;Follow-up
What is a covering index and how does it differ from a regular index? When would it give you the biggest performance gain?