What is a database index, and what are its costs? How do you decide which columns to index?

What the interviewer wants to hear

• Index is a data structure (usually B-tree) for fast lookups
• Speeds up WHERE, JOIN and ORDER BY on indexed columns
• Costs: extra storage and slower inserts and updates
• Index based on real query patterns and selectivity
• Use EXPLAIN to verify the index is used

Model answer

An index is a separate data structure, usually a B-tree, that keeps column values sorted with pointers to rows. Instead of a full table scan, the database can seek directly to matching rows, which makes WHERE filters, joins and ORDER BY much faster. The costs are extra disk and memory, and slower writes, because every INSERT, UPDATE and DELETE must also update each index. So I do not index every column. I look at the slow and frequent queries, check them with EXPLAIN or EXPLAIN ANALYZE, and index columns used in filters and joins, especially foreign keys. Columns with high selectivity, like email, benefit most. For queries that filter on several columns I use a composite index with the most commonly filtered column first. Then I confirm with EXPLAIN that the plan really uses the index.

More easy Backend Developer questions

• How would you design a REST API for a simple blog with posts and comments? Mention URLs, methods and status codes.
• When would you choose a SQL database and when a NoSQL database?
• What is the difference between authentication and authorization? How would you implement both in an API?
• Explain what happens on the backend from the moment a request hits your server until a response is sent.
• What is a database transaction, and what do the letters in ACID mean? Give an example where you need one.
• What are logs, metrics and traces, and how would you use each one to run a backend service?
• How would you test a new POST /orders endpoint? Which kinds of tests would you write?
• Why do backend teams package services with Docker, and what makes a good Dockerfile?
• Explain the HTTP status code classes and give examples of codes a REST API commonly returns.