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

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? ```hint 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. ``` #### 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? ```hint Measure before changing Before rewriting anything, find out what the database actually does when it runs the query, and which step consumes the time. ``` #### 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 ### What a Strong Answer Covers - A correct description of B-tree indexes, including range scans and the column order of composite indexes - The read-write trade-off stated concretely: write amplification, storage, selectivity - A systematic, measurement-driven tuning method rather than a list of tips - Awareness of statistics, predicates that cannot use an index, and join strategies - Verification and safety: comparing plans, testing on realistic data, not executing destructive statements while analyzing them ### 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?