Skip to the page
Tecyfy

SQL interview question · hard

How do you optimize a slow-performing SQL query? What tools or techniques would you use?

The short answerFind the queries that actually cost the most, read their execution plans to see where the time goes, then fix the cause, usually a missing or unusable index, too much data read, a bad join or stale statistics, and measure again.

The full answer

Find the queries that actually cost the most, read their execution plans to see where the time goes, then fix the cause, usually a missing or unusable index, too much data read, a bad join or stale statistics, and measure again.

1. Find the right query Look at the total time spent per query, not just the slowest single run: a 50 ms query run a million times a day matters more than one slow report.

  • PostgreSQL: pg_stat_statements.
  • SQL Server and Azure SQL: Query Store, and Extended Events (SQL Server Profiler is deprecated).
  • MySQL: the slow query log and Performance Schema.

2. Read the plan EXPLAIN shows the plan the optimizer chose; EXPLAIN ANALYZE (PostgreSQL, MySQL) or the actual execution plan (SQL Server) runs the query and shows real row counts and times per step. Look for:

  • full scans of large tables where few rows are needed;
  • estimated row counts far from the actual ones (stale statistics, or a correlation the optimizer can't see);
  • sorts and hash operations spilling to disk;
  • nested loops over large inputs.

3. Fix indexing

  • Index columns used in selective WHERE filters and join keys; for several conditions, a composite index with equality columns first.
  • A covering index (with INCLUDE columns in PostgreSQL and SQL Server) lets the query be answered from the index alone.
  • Make predicates usable by an index: WHERE order_date >= '2026-01-01' can use an index on order_date, but WHERE YEAR(order_date) = 2026 usually can't.
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);

4. Read less data

  • Select only the needed columns instead of SELECT *.
  • Filter as early as possible, and paginate with keyset pagination rather than large OFFSETs.

5. Rewrite where it helps

  • Replace row-by-row logic (cursors, loops) with set-based SQL.
  • Check that EXISTS, IN and joins express what you mean; NOT IN with NULLs returns nothing.
  • Pre-aggregate in a CTE or derived table before joining when the join would multiply rows.

6. Keep statistics current Run ANALYZE (PostgreSQL), UPDATE STATISTICS (SQL Server) or ANALYZE TABLE (MySQL) after large data changes.

7. Structural changes for very large tables

  • Partitioning by date lets the planner skip whole partitions. In PostgreSQL:
CREATE TABLE orders (
    order_id   BIGINT NOT NULL,
    order_date DATE   NOT NULL,
    total      NUMERIC(12,2),
    PRIMARY KEY (order_id, order_date)      -- must include the partition key
) PARTITION BY RANGE (order_date);

CREATE TABLE orders_2026_10 PARTITION OF orders
    FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
  • Materialized views or summary tables for repeated heavy aggregations; caching for results that change rarely.

8. Measure the result Compare the plan and the timing before and after with realistic data volumes; a change that helps on a small test table can hurt at production size.

Last updated 16 October 2026