Indexes, Query Performance and Full-Text Search

Every SQL interview past the junior level asks some version of "this query is slow — what do you do". The expected answer is a method, not a trick: look at the plan, understand why the index was not used, and fix the predicate or add the right index.

How a B-tree index works

An index is a sorted structure mapping column values to row locations. Sorted is the operative word — it is what enables three things and nothing else:

1. Equality lookup — WHERE email = 'a@b.com'
2. Range scan — WHERE created_at >= '2024-01-01', and LIKE 'A%', which is a range
3. Ordering for free — ORDER BY on the indexed columns needs no sort

The cost: every INSERT, UPDATE and DELETE must maintain every index on the table. Indexes are not free, which is the other half of the answer.

Sargability: keep the column bare

A predicate the engine can satisfy with an index seek is sargable. Wrapping the indexed column in a function destroys that:

-- Not sargable: the function must run for every row
WHERE YEAR(hire_date) = 2024
WHERE UPPER(email) = 'A@B.COM'
WHERE salary * 12 > 100000
WHERE name LIKE '%son'          -- leading wildcard: no usable prefix

-- Sargable equivalents
WHERE hire_date >= '2024-01-01' AND hire_date < '2025-01-01'
WHERE email = 'a@b.com'         -- or index UPPER(email) as an expression index
WHERE salary > 100000 / 12

The rule: the indexed column stays alone on one side of the comparison. When you truly need the transformation, index the expression itself — CREATE INDEX ON users (UPPER(email)) in PostgreSQL, or a generated column in MySQL.

Composite indexes and the leftmost-prefix rule

An index on (a, b, c) is sorted by a, then b within equal a, then c. So it serves queries filtering on a leftmost prefix — and only those:

INDEX (department, hire_date, salary)

WHERE department = 'Eng'                            ✔ uses it
WHERE department = 'Eng' AND hire_date > '2024-01-01' ✔ uses it
WHERE department = 'Eng' ORDER BY hire_date          ✔ ordering for free
WHERE hire_date > '2024-01-01'                       ✘ skips the leading column
WHERE salary > 100000                                ✘ skips two

Ordering rule of thumb: **equality columns first, then the range column, then anything used only for ordering.** A range predicate stops the index being usable for columns to its right, which is why it goes last.

Covering indexes are the follow-up worth volunteering: if the index contains every

column the query needs, the engine never touches the table at all — an index-only scan. That is also the concrete reason to avoid SELECT *.

Reading EXPLAIN

EXPLAIN ANALYZE
SELECT e.name, d.name FROM employees e
JOIN departments d ON d.id = e.department_id
WHERE e.salary > 100000;

What to look for, roughly in order of how often it is the problem:

• Seq Scan / Full Table Scan on a large table with a selective filter — a missing or

unusable index. (On a small table, a scan is correct and faster than an index.)

• Rows estimated vs actual far apart — stale statistics; run ANALYZE. A bad estimate

causes a bad plan choice, which is the real problem.

• Nested Loop over a large outer input — often the sign of a missing index on the join

key.

• Sort or Hash spilling to disk — memory limits, or a sort that a suitable index

would have avoided.

EXPLAIN shows the plan; EXPLAIN ANALYZE actually runs the query and shows real timings and row counts. The comparison between estimated and actual is the most useful single number in the output.

The usual causes of a slow query

Foreign key columns are the most commonly missing index: most engines index the parent key automatically but not the child's referencing column, so every join and every cascading delete scans.

Full-text search

LIKE '%term%' cannot use a B-tree, is a full scan, and does no stemming or ranking. For real text search, use the built-in full-text index:

-- PostgreSQL
CREATE INDEX idx_docs_fts ON documents USING GIN (to_tsvector('english', body));
SELECT title FROM documents
WHERE to_tsvector('english', body) @@ plainto_tsquery('english', 'database index')
ORDER BY ts_rank(to_tsvector('english', body), plainto_tsquery('database index')) DESC;

-- MySQL
ALTER TABLE documents ADD FULLTEXT INDEX ft_body (body);
SELECT title FROM documents WHERE MATCH(body) AGAINST('database index' IN NATURAL LANGUAGE MODE);

Full-text handles stemming ("running" matches "run"), stop words and relevance ranking — none of which LIKE does. For fuzzy substring matching specifically, PostgreSQL's pg_trgm trigram index makes even LIKE '%son%' indexable. The honest answer for search at scale is a dedicated engine such as Elasticsearch, and saying where that line falls is part of a good answer.

Mistakes that cost the interview

• "Add an index" as the whole answer. Explain which columns, in which order, and why.
• Ignoring write cost. Every index slows down writes and consumes space.
• Indexing a low-cardinality column alone — an index on a boolean rarely helps.
• Forgetting the foreign-key index on the child table.
• Trusting `EXPLAIN` without `ANALYZE`, so estimates go unchecked.
• Optimising without measuring first.

Practise the query patterns on the SQL sheet.