yoklainterview sim
ProgressHistory

Database Query Optimization Interview Questions

75 verified Database Query Optimization interview questions — solve with answers, learn from explanations, test yourself in a real simulation.

Try the real simulation →

Sample questions

Query OptimizationDifficulty 1
What does the SQL EXPLAIN command primarily show for a query?
  • aThe rows the query will return, without actually running it against the data
  • bA list of every index defined on the tables referenced by the query
  • cThe execution plan the optimizer chose, such as scan and join methods✓
  • dThe exact wall-clock time the query took the last time it ran
Explanation:EXPLAIN reports the planner's chosen execution plan — which scans (sequential vs index), join methods, and order it intends to use, along with cost and row estimates — without returning the actual rows (a). It does not list all indexes (b); it shows only the ones the plan uses. Plain EXPLAIN estimates and does not execute, so it reports no real timing (d) — that requires EXPLAIN ANALYZE.
Query OptimizationDifficulty 1
In a query execution plan, what does an index scan mean?
  • aThe engine walks an index to locate matching rows instead of reading the whole table✓
  • bThe engine loads the entire table into memory and then searches through it one row at a time
  • cThe engine rebuilds the index before answering the query
  • dThe engine returns rows in random physical order to save time
Explanation:An index scan uses the index structure (typically a B-tree) to jump to the matching rows, avoiding a full read of the table. Rebuilding the index (c) is a maintenance operation, not part of answering a query. Loading the whole table and scanning it (b) describes a sequential scan, the opposite approach.
Query OptimizationDifficulty 2
For which case is a sequential (full table) scan often a better plan than an index scan?
  • aLooking up a single row by its primary key in an extremely large table
  • bFetching one user by a unique, highly selective email column
  • cA large table where the filter column has a dedicated index
  • dA small table, or a query that returns a large fraction of the rows✓
Explanation:Index scans win when a query touches a small, selective slice of a big table. When the table is tiny or the query returns most of the rows, reading everything sequentially is cheaper than jumping back and forth between index and table for nearly every row. Single-row primary-key or selective-email lookups (a, b) are the classic cases where an index scan is clearly better.
Query OptimizationDifficulty 1
Why is SELECT * generally discouraged in performance-sensitive queries?
  • aIt always returns the rows in the wrong order
  • bIt fetches every column, adding I/O and network cost and blocking index-only plans✓
  • cIt silently drops rows that contain any NULL value
  • dIt holds an exclusive lock on the whole table until the client finishes reading every result row
Explanation:SELECT * pulls all columns even when only a few are needed, wasting I/O and network bandwidth and preventing index-only scans (where the index alone can satisfy the query). It does not change row order (a), drop NULL-containing rows (c), or lock the table (d).
Query OptimizationDifficulty 2
orders.created_at has a B-tree index, but this query does a full scan anyway:
SELECT * FROM orders
WHERE EXTRACT(YEAR FROM created_at) = 2024;

Why, and what is the standard fix?
  • aWrapping the column in a function hides it from the index; rewrite it as a range on created_at✓
  • bThe index is corrupted and must be rebuilt before the query can use it
  • cEXTRACT is not permitted inside a WHERE clause, so the planner silently ignores the whole index
  • dB-tree indexes never support date columns; a hash index is required here
Explanation:An index on created_at stores raw column values, not EXTRACT(YEAR FROM created_at). Once the column is inside a function, the engine can't match it against the index and falls back to a scan. Rewrite it as a sargable range: created_at >= '2024-01-01' AND created_at < '2025-01-01'. The index is not corrupted (b), EXTRACT is valid in WHERE (c), and B-trees handle date columns fine (d).
Query OptimizationDifficulty 2
customers.name has a B-tree index. Two queries:
-- Q1
SELECT * FROM customers WHERE name LIKE 'Ali%';
-- Q2
SELECT * FROM customers WHERE name LIKE '%Ali';

Which can use the index, and why?
  • aBoth can, because both use the LIKE operator on an indexed column
  • bNeither can, because LIKE always forces a full table scan
  • cOnly Q1: a known prefix lets the engine seek within the ordered index✓
  • dOnly Q2: matching the end of a string is what B-trees are optimized for
Explanation:A B-tree stores names in sorted order, so a fixed prefix ('Ali%') lets the engine seek straight to that range. A leading wildcard ('%Ali') has no known starting point, so the engine must examine every row — the index can't help. LIKE is not inherently a full scan (b); it depends on whether the pattern is anchored at the start.

By specialization

MongoDB 450MySQL 450PostgreSQL 450Redis 450

By seniority

Database JuniorDatabase MidDatabase Senior

Other fields

BackendFrontendFullstackDevOps / CloudAI EngineerMobileSecurityQA / Test AutomationData EngineerGame DevelopmentML Engineer

Test yourself against the 2475-question Database bank.

Start interview