Katkat
Back

Relational Database Indexes: Read/Write Trade-offs and Tuning a Slow SQL Query

EngelhartSoftware Engineer · Technical Screen
MediumSoftware EngineerTechnical ScreenSoftware Engineering Fundamentals

Last updated

A database fundamentals interview question on relational indexes: what an index is, why it speeds up queries, why not every column should be indexed and how indexes affect write performance. It then asks how you would troubleshoot and tune a complex SQL query that runs extremely slowly.

Explain what a relational database index is and how it affects reads and writes, then describe how you would troubleshoot and tune a complex SQL query that runs very slowly. These came in the fundamentals half of a technical screen for a junior quantitative developer role, as part of a fast oral sequence.

No schema was given. Where an example helps, assume a table such as trades(trade_id, product, trade_time, quantity, price).

Clarifying Questions

  • Which database engine is it (PostgreSQL, MySQL, SQL Server, a columnar warehouse)? Plan tools and index types differ.
  • Is the slow query a transactional lookup or an analytical report over large tables?
  • Has it always been slow, or did it slow down recently?
  • May the schema and indexes change, or only the query text?

Part 1 — Indexes

  1. What is an index in a relational database?
  2. Why does it make queries faster?
  3. Why not create an index on every column?
  4. How do indexes affect write performance?

What This Part Should Cover

  • The structure of a typical index and what it stores
  • The kinds of queries it accelerates, and the ones it does not
  • The storage, write and maintenance costs that argue against indexing everything

Part 2 — Tuning a slow, complex query

If a very complex SQL query is extremely slow, how would you troubleshoot and tune it?

What This Part Should Cover

  • A diagnostic process that starts from the execution plan and actual row counts
  • The common causes such a plan reveals, and a fix for each
  • Statistics, query rewrites, index changes and data-layout changes, in a sensible order
  • How to verify the improvement and check for side effects

Hints

Hint 1

Count the pages: Compare how much of the table the database must read to find one row with and without a sorted lookup structure, and then list everything that has to be updated when a row is inserted.

Hint 2

Measure before changing: Before rewriting anything, find out what the database actually does when it runs the query, and which step consumes the time.

Follow-up questions

  • A composite index on `(product, trade_time)` exists. Which of these filters can use it: product alone, trade time alone, or both?
  • A query filters with `WHERE date(trade_time) = '2026-01-15'` and does not use the index on `trade_time`. Why, and how do you fix it?
  • When does the optimizer choose a full table scan even though an index exists on the filtered column?
  • A table receives a heavy stream of inserts and also serves reporting queries. How do you balance indexing between the two?