Query Performance for SQL Interviews: Indexes, Scans and Efficient SQL
The biggest misconception about SQL performance is that an index is a magic speed button. In real systems, the same index can make one query fly, slow down another, and silently increase write cost every time a row is inserted or updated.
- Query performance is about how much work the database does to return the correct result - not just whether the SQL runs.
- A sequential scan reads a table broadly; an index scan uses a smaller lookup structure to reach matching rows faster.
- Indexes help most when predicates are selective, stable, and aligned with
WHERE,JOIN,ORDER BY, orGROUP BY. - The optimizer chooses a plan using table statistics, estimated rows, available indexes, and cost assumptions.
- Write efficient SQL by filtering early, avoiding unnecessary columns, using sargable predicates, preventing row explosion, and reading the execution plan.
- The tuning loop is: measure, inspect plan, isolate bottleneck, rewrite or index, then measure again.
- The most common interview trap is saying “add an index” before explaining selectivity, scan cost, and write trade-offs.
The Big Picture
A SQL query is not executed exactly as you write it. The database parses it, estimates different access paths, chooses a plan, then performs physical work - reading pages, joining rows, sorting, aggregating, and returning results. Performance tuning means reducing unnecessary work without changing the business answer.
Core Explanation: Indexes, Scans and Efficient SQL
Think of a table as a large book. A sequential scan is reading many pages to find the rows you need. An index is like a sorted lookup at the back of the book - useful when the lookup narrows the search enough to justify using it.
An index does not store “faster data.” It stores an ordered access structure, commonly a B-tree in relational databases, that helps the engine find row locations using indexed columns. The win comes from fewer pages read, fewer rows examined, and sometimes avoiding a sort.
Why an Index Sometimes Loses to a Sequential Scan
If a query returns a large share of the table, repeatedly jumping from index entries to table rows can be slower than reading the table in order. This is why the right answer is not “index everything” - it is “match the access path to the workload.”
The Five-Step Tuning Loop
Good SQL tuning is a loop, not a one-time guess. You measure the current state, inspect the execution plan, change one thing, and prove that the result improved.
How to Write Efficient SQL
Efficient SQL is not “short SQL.” It is SQL that gives the optimizer a clear, low-cost path to the answer.
Worked Example: Why Composite Index Order Matters
Suppose an Indian payments dashboard needs this query for one merchant:
SELECT payment_id, amount, status FROM payments WHERE merchant_id = 42 AND created_at >= CURRENT_DATE - INTERVAL '7 days' ORDER BY created_at DESC;
Assume the payments table has 10,000,000 rows, and this merchant has 2,000 payments in the last seven days. Without a useful index, the database may examine close to 10,000,000 rows to return 2,000 rows.
A composite index such as (merchant_id, created_at) gives the engine a direct path: first narrow to one merchant, then scan the recent date range in order. The work ratio improves from roughly 10,000,000 / 2,000 = 5,000 examined rows per returned row to much closer to the actual result set. In interviews, this example proves you understand both selectivity and index column order.
For an Indian payments company such as Razorpay, merchant dashboards often need filtered views by merchant, date, payment status, refunds, and settlements. The performance lesson is that a query like “show failed payments for this merchant this week” should be supported by indexes that match the business access pattern, not random single-column indexes. The primary driver is selective merchant-date filtering, supported by good composite indexes, fresh statistics, and avoiding unnecessary columns in dashboard queries.
Definitions You Should Be Able to Say Cleanly
- Query performance: The time and resources a database uses to return the correct result for a SQL query.
- Index: A database structure that speeds row lookup by maintaining ordered values and references to table rows.
- Sequential scan: A plan that reads a table broadly and checks rows against the query condition.
- Index scan: A plan that uses an index to locate candidate rows before fetching required data.
- Selectivity: The fraction of table rows matched by a predicate; lower fraction means more selective.
- Sargable predicate: A search condition written so the database can use an index efficiently.
- Covering index: An index containing all columns needed by a query, reducing or avoiding table-row fetches.
Metrics to Track When Tuning SQL
Use these metrics together. A query can have acceptable execution time today but still be risky if it scans too many rows or depends on a warm cache.
Case Study: GitLab and Performance Discipline in SQL
GitLab is a strong SQL performance case because its product depends on fast issue, merge request, project, and activity queries across large multi-tenant datasets.
Situation: GitLab is a collaboration platform where users expect project pages, issue lists, merge requests, and activity feeds to load quickly even as teams create more records over time. In such a product, a slow query is not a back-office inconvenience - it directly affects product experience.
The move: GitLab has publicly emphasized database review practices: inspect query plans, avoid inefficient queries, use appropriate indexes, prefer scalable pagination patterns, and watch for queries that may work on small development data but fail on production-scale data. The primary driver is disciplined query review before production impact. Supporting drivers include indexing aligned to access patterns, avoiding row explosion, careful pagination, and monitoring real execution behavior.
Outcome or lesson: The lesson is not that one index saved the product. The lesson is that performance is managed as an engineering process: plan visibility, schema design, query review, and production monitoring reinforce each other.
Strategic so what: In an interview, use GitLab to show maturity: scalable SQL is not a syntax trick; it is the combination of access-pattern design, query plan literacy, indexing discipline, and measurement.
How AI Changes Query Performance: Indexes, Scans and Writing Efficient SQL
AI does not remove the need to understand indexes and scans. It raises the baseline by making plan interpretation and query review faster - but it can also suggest confident, wrong indexes if you give it no workload context.
- AI-assisted plan explanation: LLM tools can translate
EXPLAIN ANALYZEoutput into plain English: which node is expensive, where row estimates are wrong, and whether the query is scanning too much data. - Index recommendation with workload context: Modern database tools and AI copilots can suggest candidate indexes, but the student answer must still check selectivity, write cost, and whether the index duplicates an existing one.
- Natural-language SQL review: Analysts increasingly ask tools to review SQL for anti-patterns such as
SELECT *, non-sargable filters, accidental cross joins, deep offsets, and missing join predicates.
Paste your table schema, query, and anonymized EXPLAIN output into ChatGPT. Ask: “Identify the likely bottleneck, explain whether an index helps, suggest one rewrite, and state the trade-off.” Then verify the suggestion against selectivity and write cost instead of accepting it blindly.
Interview Relevance
“You wrote a SQL query for a dashboard and it is slow. How would you diagnose and improve it? Also explain when an index may not help.”
Use one concrete line in your answer: “I would not add an index blindly; I would first check whether the predicate is selective and whether the plan is actually doing an expensive scan.” That sentence separates you from syntax-only candidates.
Common Mistake
The mistake: Saying “add an index” as the universal solution. It costs candidates because it ignores selectivity, optimizer choice, write overhead, storage, and whether the SQL is even written in an index-friendly way. One-line fix: Always say “measure the plan first, then choose between rewriting SQL, indexing, or redesigning the access pattern.”
What to Revise Next
Now that you understand how databases execute SQL, move from performance thinking to interview execution. Revise Ten SQL Patterns That Cover Most Interview Questions to build query fluency, then study Case Study: Answering a Business Question End to End in SQL to connect SQL logic with business decisions.