MediumExperience level: 3-5 years · 8-15 years1 min read

What is a database index and what is the trade-off?

Reviewed by Gurusankar M.

Asked inAmazonDeloitte
Report issue
On this page

Concept Made Simple

Coffee Chat Question

“What is a database index and what is the trade-off?”

Remember It Faster

Mind Map Answer

An index is like a book's index — instead of scanning every page (row), the DB jumps straight to the data.

Speeds up
SELECT / WHERE / JOIN lookups
Slows down
INSERT / UPDATE / DELETE (index upkeep)
Costs
Extra storage

Learn by Doing

Hands-on Keyboard

sql
CREATE INDEX idx_users_email ON users(email);
-- now WHERE email = ? uses a B-tree seek, not a full scan

Think Beyond the Expected

What If?

Why might the database ignore your index?

If a query returns a large fraction of the table, a full scan is cheaper than many random index seeks — so the optimizer skips the index. Low-selectivity columns rarely benefit.

Continue Learning with AI

Take this question deeper with your favourite AI assistant. Pick a depth, copy the prompt, or open it directly — AI is your learning companion, not a shortcut.

Plain-language foundations

I'm preparing for a software engineering interview and want to understand this from scratch, as a beginner.

Topic: Indexes (SQL)
Interview question: "What is a database index and what is the trade-off?"

Please:
1. Explain the core idea in simple, plain language, using an everyday analogy.
2. Define any technical terms you use.
3. Walk through one small, concrete example.
4. Finish with a single sentence I can easily remember.

Keep the tone friendly and assume I'm new to this topic.
Open inChatGPTGeminiClaude

Was this answer helpful?

Affiliate links

Tools for Your Developer Workspace

Equipment for a comfortable, productive setup.

As an Amazon Associate I earn from qualifying purchases.