SQL Query Optimization Interview Questions

Optimisation questions are where interviewers find out whether you understand what a database does with your query. "Add an index" is not an answer on its own; the follow-ups are why the index helps, when it will not be used, and how you would prove it. These query optimization interview questions are answered with evidence: the query plan, and a count of how much work the database actually did.

The data is the UCI household electric power set: one reading a minute from a house in Sceaux, near Paris, from December 2006 to November 2010, about two million rows after dropping the minutes with missing values (UCI: household power consumption). SQLite runs in-process from Python, which makes two kinds of evidence easy to collect. EXPLAIN QUERY PLAN shows the plan. Python's set_progress_handler calls back every N virtual machine instructions, which gives a work count that is the same on every run, unlike a stopwatch (Python: sqlite3).

import csv
import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE readings (day TEXT, minute TEXT, power REAL, voltage REAL)")
with open("household_power_consumption.txt", newline="") as f:      # UCI, one household near Paris, 2006-2010
    rows = csv.reader(f, delimiter=";")
    next(rows)
    db.executemany("INSERT INTO readings VALUES (?, ?, ?, ?)",
                   (("{2}-{1:0>2}-{0:0>2}".format(*d.split("/")), t[:5], float(p), float(v))   # d/m/yyyy -> yyyy-mm-dd
                    for d, t, p, _, v, *_ in rows if p != "?"))
print(f"{db.execute('SELECT COUNT(*) FROM readings').fetchone()[0]:,} readings loaded")

work = 0


def count():                        # SQLite calls this every 100 virtual machine instructions
    global work
    work += 1


db.set_progress_handler(count, 100)


def run(label, sql, params=()):
    global work
    plan = " | ".join(row[3] for row in db.execute("EXPLAIN QUERY PLAN " + sql, params))
    work = 0
    result = db.execute(sql, params).fetchall()
    shown = result[0] if len(result) == 1 else f"{len(result)} rows"
    print(f"  {label:<26} {work * 100:>11,} instructions  {plan}  -> {shown}")


FEB = "SELECT ROUND(AVG(power), 3) FROM readings WHERE day >= '2008-02-01' AND day < '2008-03-01'"
print("Q1/Q2 average power in February 2008")
run("no index", FEB)
db.execute("CREATE INDEX idx_day ON readings (day)")
run("index on day", FEB)
run("function on the column", "SELECT ROUND(AVG(power), 3) FROM readings WHERE substr(day, 1, 7) = '2008-02'")

print("Q3 composite index (day, minute): which queries can use it?")
db.execute("CREATE INDEX idx_day_minute ON readings (day, minute)")
run("day and minute", "SELECT power FROM readings WHERE day = '2008-02-14' AND minute = '19:00'")
run("minute only", "SELECT ROUND(AVG(power), 3) FROM readings WHERE minute = '19:00'")

print("Q4 covering index")
db.execute("CREATE INDEX idx_day_power ON readings (day, power)")
run("index holds every column", FEB)

print("Q5 'is there any?': COUNT(*) vs EXISTS")
run("COUNT(*) > 0", "SELECT COUNT(*) > 0 FROM readings WHERE voltage < 230")
run("EXISTS", "SELECT EXISTS (SELECT 1 FROM readings WHERE voltage < 230)")

print("Q6 rows 1,000,001-1,000,010: OFFSET vs keyset pagination")
run("OFFSET", "SELECT rowid FROM readings ORDER BY rowid LIMIT 10 OFFSET 1000000")
run("keyset (rowid > last seen)", "SELECT rowid FROM readings WHERE rowid > 1000000 ORDER BY rowid LIMIT 10")

2,049,280 readings loaded
Q1/Q2 average power in February 2008
  no index                     9,195,000 instructions  SCAN readings  -> (1.181,)
  index on day                   250,600 instructions  SEARCH readings USING INDEX idx_day (day>? AND day<?)  -> (1.181,)
  function on the column       8,322,400 instructions  SCAN readings  -> (1.181,)
Q3 composite index (day, minute): which queries can use it?
  day and minute                       0 instructions  SEARCH readings USING INDEX idx_day_minute (day=? AND minute=?)  -> (0.95,)
  minute only                  6,152,100 instructions  SCAN readings  -> (1.583,)
Q4 covering index
  index holds every column       208,800 instructions  SEARCH readings USING COVERING INDEX idx_day_power (day>? AND day<?)  -> (1.181,)
Q5 'is there any?': COUNT(*) vs EXISTS
  COUNT(*) > 0                 8,202,200 instructions  SCAN readings  -> (1,)
  EXISTS                           6,100 instructions  SCAN CONSTANT ROW | SCALAR SUBQUERY 1 | SCAN readings  -> (1,)
Q6 rows 1,000,001-1,000,010: OFFSET vs keyset pagination
  OFFSET                       2,000,000 instructions  SCAN readings  -> 10 rows
  keyset (rowid > last seen)           0 instructions  SEARCH readings USING INTEGER PRIMARY KEY (rowid>?)  -> 10 rows

Q1. How do you find out why a query is slow?

Read the plan before changing anything. In SQLite that is EXPLAIN QUERY PLAN; in PostgreSQL EXPLAIN (ANALYZE, BUFFERS); in SQL Server the actual execution plan. Look for full scans of large tables, the estimated against actual row counts, and expensive sorts. The February average without an index was a SCAN readings: every one of the two million rows was read to find 29 days. After adding an index on day, the plan became SEARCH ... USING INDEX and the work fell from about 9.2 million instructions to about 0.25 million, roughly 37 times less, for the same answer of 1.181 kilowatts.

What the interviewer wants to hear: a method. Measure, read the plan, change one thing, measure again.

Q2. Why is my index not being used?

The most common cause is a function or calculation on the indexed column. WHERE substr(day, 1, 7) = '2008-02' asks for the same rows as the date range, but the index is ordered by day, not by substr(day, 1, 7), so the database cannot seek into it. The plan went back to SCAN and the work to 8.3 million instructions. A filter the index can serve is called sargable. Rewrite such filters as ranges on the bare column: day >= '2008-02-01' AND day < '2008-03-01'.

Other causes worth naming: comparing the column with a value of a different type, a leading wildcard in LIKE '%...', a column that is not selective enough to be worth an index, or out-of-date statistics.

Q3. Does column order matter in a composite index?

Yes. A multi-column index is sorted by its first column, then by the second within each value of the first, like a phone book sorted by surname, then first name (SQLite: query planning). The index on (day, minute) served a query with both columns in one seek, too little work to register. Asked for minute = '19:00' alone, it could not help, because 19:00 readings are scattered across every day, and the query scanned the table. The optimizer documentation states the rule: if the left-most column is not constrained, the index is not usable (SQLite: optimizer overview).

Rule of thumb to state: put columns tested for equality first, and the column used for a range last.

Q4. What is a covering index?

An index that contains every column the query reads, so the database never has to look up the table rows (SQLite: query planning). With (day, power), the February average became USING COVERING INDEX and the work dropped again, from about 250,600 to 208,800 instructions. The saving grows with wider rows and slower storage, because each table lookup is a random read. This is also the honest reason to avoid SELECT *: listing only the columns you need is what makes a covering index possible.

Q5. Is COUNT(*) > 0 the same as EXISTS?

Same answer, very different work. COUNT(*) counts every matching row before comparing; EXISTS stops at the first one. Checking whether any reading fell below 230 volts, COUNT(*) > 0 scanned the whole table, about 8.2 million instructions, while EXISTS stopped after about 6,100. Use EXISTS whenever the question is "is there any?".

Q6. Why does deep pagination get slower page by page?

OFFSET 1000000 still produces and throws away the first million rows, because the database has to count past them (Use The Index, Luke: no offset). Fetching rows 1,000,001 to 1,000,010 that way cost 2 million instructions. Keyset pagination remembers the last key seen and asks for rowid > 1000000, which the primary key serves in one seek. It also avoids duplicate or skipped rows when data changes between pages.

Questions you answer by explaining

Some common questions have no single measurement, and the interviewer wants your reasoning:

• What is the N+1 problem? Code that runs one query for a list, then one more query per item. Each query is cheap, but each one is a round trip to the server, so latency multiplies by N. Replace it with one join or one GROUP BY. In-process SQLite has no network, which is why the cost does not show up in an instruction count, and why you should measure it from the application.
• Why would the optimiser ignore a good index? Its statistics may be stale, or it may estimate that the filter matches so many rows that a scan is cheaper. Update statistics (ANALYZE in SQLite and PostgreSQL) and compare estimated with actual rows.
• Can indexes make things slower? Yes. Every insert, update and delete must maintain every index, and indexes take space. Index for the queries you actually run.
• How do you optimise a query you cannot change? Indexes, statistics, partitioning, or a summary table or materialised view refreshed on a schedule.

How to answer optimisation questions

Start with evidence, not a fix. Say what you would measure, read the plan out loud, name the specific cause, and predict the effect of your change before running it. The step-by-step version of that process, on a different dataset, is in SQL optimisation for data engineers. Warehouse engines work differently, with columnar storage and partition pruning instead of B-tree seeks; see BigQuery SQL tips and optimisation for that side. For other interview topics, see SQL interview questions: top 50 with answers and SQL joins interview questions.

Query tuning is part of the SQL Server course.

Sources

• Query Planning (SQLite documentation) — SQLite
• The SQLite Query Optimizer Overview — SQLite
• sqlite3: DB-API 2.0 interface for SQLite databases (Python documentation) — Python Software Foundation
• No Offset (Use The Index, Luke) — Markus Winand
• Individual Household Electric Power Consumption (UCI Machine Learning Repository) — UCI Machine Learning Repository