What is a database index, and how would you decide whether a column needs one?

What the interviewer is looking for

• This question tests databases & data modelling. Chooses schemas and indexes for real query patterns, understands transactions, and describes safe migrations on live data.
• Strong answers: Faster lookups at the cost of storage and slower writes, indexing columns used in frequent filters and joins, and checking query plans.
• Likely follow-up: How would you confirm the database is actually using your index?

Another way it may be asked: Your users table has a million rows and searching by email is slow. What could an index do, and is there a downside?

A structure to follow: Frame, structure, analyse, recommend

1. Step 1

   Frame

   Restate the goal and ask what success is and what the constraints are.

2. Step 2

   Structure

   Split the problem into a few separate parts you can check one by one, and say what you will look at first and why.

3. Step 3

   Analyse

   Reason with the information you have, state your assumptions out loud, and say what data would change your mind.

4. Step 4

   Recommend

   Give a clear recommendation, the main risk, and the first step you would take.

This is guidance on shape, not a script. Use your own real experience and facts; never invent details to fill a gap. See the STAR guide and answer frameworks.

Common slips

• Listing ideas without choosing between them.
• Hiding assumptions instead of stating them.
• Finishing without a recommendation.

How to practise it

Answer in four spoken parts, one per step, then check you ended on a recommendation.

Related questions

• Tell me about a time you changed a database schema on a live system. How did you do it without downtime?
• If you were building an API for a simple to-do app, what endpoints would you create, and how would you handle errors?
• How do you protect a web application from someone sending malicious input, such as an SQL injection attack?
• An endpoint starts timing out in production at peak hours. How would you investigate, and what would you look at first?
• How do you handle two requests trying to update the same record at the same time?

Browse all questions