MediumSQL#1457 min readJul 11, 2026

I/O Bottlenecks

practicelearning

Practice modeTest yourself instead of reading straight through

Hook: Interviewers love this question because it separates 'I added an index' from 'I understand why the query was slow'.

Question: What are I/O bottlenecks in SQL, and how do indexes help reduce them?

Answer: An I/O bottleneck happens when a query spends most of its time waiting for data to be read from or written to storage, instead of using CPU. In SQL, that usually means too many table pages are being scanned, too many random row lookups are happening, or the engine is spilling work to disk for sorts and joins. Indexes help by letting the database jump to a small set of pages instead of reading the whole table.

Interview-Ready Answer: I/O bottlenecks are slowdowns caused by excessive disk or page reads and writes. In SQL, that often means a full table scan, lots of random lookups, or temp-file spills during sorting and joining. I reduce I/O by making predicates searchable with good indexes, especially selective and covering indexes, so the engine reads far fewer pages. A key detail is that indexes are not free: they speed reads but add storage and write overhead, so I choose them based on the real query shape.

Memory map — visual summary of this topic — 🧠 Memory Map

What an I/O bottleneck really means

I/O means input/output: reading and writing data pages. A page is the small block size the database moves around, commonly 8 KB in PostgreSQL and 16 KB in InnoDB/MySQL. If the needed page is not already in memory, the engine must fetch it from storage, and that wait is often much slower than CPU work.

How the database gets from a query to pages on disk

The optimizer reads your SQL and estimates which plan will touch the fewest pages.
If your WHERE clause matches the left side of an index and is selective, the engine can do an index seek or index range scan instead of reading every row.
The index points to the matching rows or, in some engines, can satisfy the whole query itself if it is a covering index (an index that contains every column the query needs).
If the needed data is not in cache, the database performs physical reads from disk or SSD. Many small random reads are usually worse than fewer sequential reads.
If the query needs a big sort, hash join, or aggregation and memory is not enough, the engine may spill to temp storage, which is another I/O cost.

Why indexes help

An index is like a book’s table of contents. Instead of reading every page to find one chapter, the engine jumps straight to the right section. That is why indexes are so powerful for selective lookups, such as finding one customer’s orders or one user’s email.

Approach	Reads	Best for	Trade-off
Table scan	All pages	Large result sets	Simple, but lots of I/O
Index seek	Index pages + row lookups	Small result sets	Random reads, write cost
Covering index	Mostly index pages	Hot read paths	Larger index, more writes

When to use an index, and when not to

Use an index when the filter is selective, the query is frequent, and the access pattern is stable.
Do not expect an index to help when the query returns a large part of the table; a sequential scan can be cheaper because it reads pages in order.
Avoid non-sargable predicates (predicates the optimizer cannot search efficiently), such as wrapping the indexed column in a function like DATE(created_at) or using a leading wildcard like %abc.
Prefer composite indexes when filters and sorts happen together, but keep the leading column order aligned with the most common search pattern.

Performance and complexity

Very roughly, a table scan is O(N) in rows or pages touched, while an index lookup is closer to O(log N) to find the leaf, plus any extra row fetches. In real life, the constant factors matter: a full scan on a warm cache can be surprisingly fast, while an index plan that causes thousands of random reads can be slower than scanning. A good rule of thumb is that indexes shine when they let you read a tiny fraction of the table; once you need a meaningful chunk of the table, scans often win.

Important edge cases

Cache can hide I/O pain: a query may look fine in dev because everything fits in memory, then slow down badly in production when cache misses increase.
Write-heavy tables pay a price: every insert, update, or delete must also update indexes, which adds write I/O.
Too many indexes hurt: they increase storage, maintenance, and checkpoint work.
Covering is engine-specific: some engines call it a covering index, others use different terms, but the idea is the same: avoid extra table fetches.

Memory model: Read fewer pages, and the database gets faster. That is the whole game.

Real-World Story: Imagine an e-commerce checkout service that shows a customer their recent orders while they finish payment. The team adds a simple index on created_at, but the real query filters by customer_id and sorts by created_at. During a holiday sale, p95 latency jumps from 40 ms to 2 seconds, the database CPU stays oddly low, and disk reads spike hard. The logs show lots of full scans and temp-file activity, which tells you the problem is I/O, not raw CPU.

The misunderstanding causes a real user-visible incident: customers click Place Order, the page spins, and some requests time out. Support sees duplicate attempts because users refresh, which creates more load and even more reads. The fix is usually a better composite index that matches the real filter order, plus a query rewrite that avoids functions on indexed columns.

SQL

-- SQLite demo: how query shape changes whether the engine can avoid extra I/O.
-- This script is self-contained and can be run as-is in sqlite3.

DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
  order_id      INTEGER PRIMARY KEY,
  customer_id   INTEGER NOT NULL,
  created_at    TEXT NOT NULL,   -- ISO-8601 timestamps sort correctly as text.
  total_amount  REAL NOT NULL,
  shipping_city TEXT NOT NULL
);

INSERT INTO orders (order_id, customer_id, created_at, total_amount, shipping_city) VALUES
  (1, 101, '2026-07-01 08:15:00', 19.99, 'Austin'),
  (2, 101, '2026-07-01 09:30:00', 42.50, 'Austin'),
  (3, 102, '2026-07-01 10:05:00', 15.00, 'Dallas'),
  (4, 103, '2026-07-01 11:45:00', 88.10, 'Houston'),
  (5, 101, '2026-07-02 07:20:00', 12.75, 'Austin'),
  (6, 104, '2026-07-02 13:10:00', 63.40, 'El Paso'),
  (7, 101, '2026-07-03 15:00:00', 27.25, 'Austin'),
  (8, 102, '2026-07-03 16:30:00', 99.00, 'Dallas');

-- Index on created_at alone can help range filters.
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- Edge case: wrapping the column in DATE() makes the predicate non-sargable.
-- The planner cannot jump straight to a useful range as easily, so this often becomes more expensive.
EXPLAIN QUERY PLAN
SELECT order_id, customer_id, total_amount
FROM orders
WHERE date(created_at) = '2026-07-01';

-- Better: a range predicate keeps the column 'searchable' and can use the index efficiently.
EXPLAIN QUERY PLAN
SELECT order_id, customer_id, total_amount
FROM orders
WHERE created_at >= '2026-07-01'
  AND created_at <  '2026-07-02';

-- Composite index for the real access pattern: find one customer's orders, then sort by time.
-- Including total_amount makes this query cheaper because the engine can answer from the index alone.
CREATE INDEX idx_orders_customer_created_total
ON orders(customer_id, created_at, total_amount);

-- This query is a good candidate for a covering index: no table lookup needed for these columns.
EXPLAIN QUERY PLAN
SELECT customer_id, created_at, total_amount
FROM orders
WHERE customer_id = 101
ORDER BY created_at;

-- Failure path: asking for shipping_city forces the engine to visit the table, because that column is not in the covering index.
EXPLAIN QUERY PLAN
SELECT customer_id, created_at, total_amount, shipping_city
FROM orders
WHERE customer_id = 101
ORDER BY created_at;

-- A quick sanity check query to show the actual rows returned.
SELECT customer_id, created_at, total_amount
FROM orders
WHERE customer_id = 101
ORDER BY created_at;

Follow-up & Tricky Questions:

How do you tell if a query is I/O bound or CPU bound? Look at the symptoms: high disk reads, temp-file spills, and low CPU usually point to I/O; high CPU with low reads often points to expensive joins, sorts, or expressions.
Why can an index make a query slower? If the query returns a large portion of the table, the engine may do many random lookups through the index, which can be worse than one sequential scan.
What is a covering index? It is an index that contains all the columns needed by the query, so the database can answer the query without visiting the table again.
What is a non-sargable predicate? It is a condition the optimizer cannot use efficiently to search an index, such as a function on the indexed column or a leading wildcard pattern.
How does caching change the picture? A warm cache reduces physical reads, so a query that looks slow on a cold system may appear fast after repeat execution. Interviewers want you to know that cache can hide I/O costs, not remove them.
Tricky: Should I index every column in WHERE? No. Every extra index adds write cost and storage, so index only the columns that are actually selective and frequently used.
Tricky: Is a full table scan always bad? No. If the query needs most of the rows, a sequential scan can be the cheapest plan because it reads pages in order.
Tricky: Will ORDER BY automatically use an index? Only if the index order matches the filter and sort pattern closely enough. If not, the engine may still need to sort.

Common Mistakes:

Adding an index blindly: Fix the query pattern first, then add the smallest useful index.
Forgetting write cost: More indexes mean slower inserts, updates, and deletes.
Using functions on indexed columns: Rewrite predicates so the engine can search the raw column value.
Assuming cache equals no I/O problem: Production traffic, cold starts, and larger tables can still expose the bottleneck.

Memory Hook: Index = table of contents, I/O = page reads. If the database can jump to the right page instead of reading the whole book, it wins.

Cheat Sheet:

SQL I/O bottlenecks usually mean too many page reads or temp-file writes.
Indexes help most when the filter is selective.
Use composite indexes that match WHERE plus ORDER BY.
Prefer sargable predicates: compare raw columns, not wrapped expressions.
Covering indexes remove extra table lookups.
Scans can beat indexes when many rows are needed.

Practice Tasks:

Write one query that is I/O heavy, then rewrite it to use a range predicate.
Design a composite index for a query that filters by one column and sorts by another.
Take one read-heavy query and make it covering by adding only the columns it truly needs.

Back to Questions←→to navigate

Approach

Reads

Best for

Trade-off

Table scan

All pages

Large result sets

Simple, but lots of I/O

Index seek

Index pages + row lookups

Small result sets

Random reads, write cost

Covering index

Mostly index pages

Hot read paths

Larger index, more writes

-- SQLite demo: how query shape changes whether the engine can avoid extra I/O. -- This script is self-contained and can be run as-is in sqlite3. DROP TABLE IF EXISTS orders; CREATE TABLE orders ( order_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, created_at TEXT NOT NULL, -- ISO-8601 timestamps sort correctly as text. total_amount REAL NOT NULL, shipping_city TEXT NOT NULL ); INSERT INTO orders (order_id, customer_id, created_at, total_amount, shipping_city) VALUES (1, 101, '2026-07-01 08:15:00', 19.99, 'Austin'), (2, 101, '2026-07-01 09:30:00', 42.50, 'Austin'), (3, 102, '2026-07-01 10:05:00', 15.00, 'Dallas'), (4, 103, '2026-07-01 11:45:00', 88.10, 'Houston'), (5, 101, '2026-07-02 07:20:00', 12.75, 'Austin'), (6, 104, '2026-07-02 13:10:00', 63.40, 'El Paso'), (7, 101, '2026-07-03 15:00:00', 27.25, 'Austin'), (8, 102, '2026-07-03 16:30:00', 99.00, 'Dallas'); -- Index on created_at alone can help range filters. CREATE INDEX idx_orders_created_at ON orders(created_at); -- Edge case: wrapping the column in DATE() makes the predicate non-sargable. -- The planner cannot jump straight to a useful range as easily, so this often becomes more expensive. EXPLAIN QUERY PLAN SELECT order_id, customer_id, total_amount FROM orders WHERE date(created_at) = '2026-07-01'; -- Better: a range predicate keeps the column 'searchable' and can use the index efficiently. EXPLAIN QUERY PLAN SELECT order_id, customer_id, total_amount FROM orders WHERE created_at >= '2026-07-01' AND created_at < '2026-07-02'; -- Composite index for the real access pattern: find one customer's orders, then sort by time. -- Including total_amount makes this query cheaper because the engine can answer from the index alone. CREATE INDEX idx_orders_customer_created_total ON orders(customer_id, created_at, total_amount); -- This query is a good candidate for a covering index: no table lookup needed for these columns. EXPLAIN QUERY PLAN SELECT customer_id, created_at, total_amount FROM orders WHERE customer_id = 101 ORDER BY created_at; -- Failure path: asking for shipping_city forces the engine to visit the table, because that column is not in the covering index. EXPLAIN QUERY PLAN SELECT customer_id, created_at, total_amount, shipping_city FROM orders WHERE customer_id = 101 ORDER BY created_at; -- A quick sanity check query to show the actual rows returned. SELECT customer_id, created_at, total_amount FROM orders WHERE customer_id = 101 ORDER BY created_at;

Follow-up & Tricky Questions:

How do you tell if a query is I/O bound or CPU bound? Look at the symptoms: high disk reads, temp-file spills, and low CPU usually point to I/O; high CPU with low reads often points to expensive joins, sorts, or expressions.
Why can an index make a query slower? If the query returns a large portion of the table, the engine may do many random lookups through the index, which can be worse than one sequential scan.
What is a covering index? It is an index that contains all the columns needed by the query, so the database can answer the query without visiting the table again.
What is a non-sargable predicate? It is a condition the optimizer cannot use efficiently to search an index, such as a function on the indexed column or a leading wildcard pattern.
How does caching change the picture? A warm cache reduces physical reads, so a query that looks slow on a cold system may appear fast after repeat execution. Interviewers want you to know that cache can hide I/O costs, not remove them.
Tricky: Should I index every column in WHERE? No. Every extra index adds write cost and storage, so index only the columns that are actually selective and frequently used.
Tricky: Is a full table scan always bad? No. If the query needs most of the rows, a sequential scan can be the cheapest plan because it reads pages in order.
Tricky: Will ORDER BY automatically use an index? Only if the index order matches the filter and sort pattern closely enough. If not, the engine may still need to sort.