How would you explain a database index to a junior developer, and how do you decide which columns to index?

What the interviewer wants to hear

• An index is a sorted lookup structure, like a book index
• It speeds up filters, joins and sorting on indexed columns
• It costs disk space and slows writes
• Choose columns from real slow queries, checked with EXPLAIN
• Foreign keys and frequently filtered columns are common candidates

Model answer

I compare it to the index at the back of a book. Without it you read every page to find a topic, which is a full table scan. With it you jump straight to the right pages. Most database indexes are B-trees: sorted copies of one or more columns with pointers to the rows, so lookups, range filters, joins and ORDER BY on those columns become fast. The cost is that every insert, update and delete must also update each index, and indexes take disk space, so you do not index everything. I choose indexes from real queries: I find slow ones in logs or monitoring, run EXPLAIN ANALYZE, and look for sequential scans on large tables. Foreign keys used in joins and columns used in WHERE clauses on busy pages, like user_id on orders, are usually the first candidates.

More easy Full Stack Developer questions

• What happens, step by step, when a user types your app URL into the browser and presses Enter?
• Design the REST API endpoints for a simple blog with posts and comments. What URLs, methods and status codes would you use?
• What is the difference between authentication and authorization? Give an example from a web app.
• You are starting a new e-commerce app. Would you choose a SQL or a NoSQL database, and why?
• Explain client-side rendering, server-side rendering and static site generation. When would you use each?
• In a web app with a React frontend and an API, where should input validation happen, and why?
• A page loads data from your API. How do you handle loading, empty and error states so users are never left confused?
• How do you manage configuration and secrets across development, staging and production for a full-stack app?
• Describe the Git and code review workflow you follow when building a feature on a team.