Understanding SQL Indexes and Query Performance

Part 14 of 16 · ~2 min

When Not to Index

The deliberate opposite of the last twelve chapters. Here is where an index is the wrong answer, with our own table as the worked example.

A low-cardinality column queried alone. As chapter 7 established, idx_emp_dept does not help dept = 'Sales' — 18% of the table is not selective enough for a seek plus 431,000 key lookups to beat a sequential scan. The qualifier is load-bearing: dept is invaluable as the leading column of a composite index. It is useless as a lone one.

A redundant index. This one costs nothing to fix and is the most common form of quiet waste. Look at what we have:

idx_emp_dept             (dept)
idx_emp_dept_covering    (dept, name, salary)
idx_emp_dept_salary_name (dept, salary, name)

Any query that can use idx_emp_dept can use either composite index instead, because dept is their leftmost column and a leftmost prefix is seekable on its own. The single-column index adds nothing that is not already there — it is strictly redundant, and it is being maintained on all 2.4 million rows anyway. Meanwhile idx_emp_dept_covering was superseded the moment chapter 5 built (dept, salary, name), which does everything it did and seeks the salary range as well.

DROP INDEX idx_emp_dept;
DROP INDEX idx_emp_dept_covering;

Two indexes gone, no query slower, and the raise run just got noticeably quicker — idx_emp_dept_covering contained salary, so dropping it removed one of the three delete-and-reinsert pairs per updated row. This is the most reliable index win available in a mature codebase, and almost nobody goes looking for it.

A small table. When the whole table already sits comfortably inside memory, scanning it start to finish costs almost nothing in absolute terms — so putting an index there weighs down every write in exchange for a speed gain too tiny for anyone to notice. Departments has 38 rows; a scan of it is essentially free. (With one exception, from chapter 10: Departments.dept is worth indexing despite the table's size, because the semi-join asks about it repeatedly and it is also the column that should have been unique in the first place.)

A column written far more often than it is queried. You pay the write cost on every change and collect the read benefit rarely or never. A last_seen_at column updated on every request and queried once a month for a report is the archetype.

A column already covered by a UNIQUE constraint or primary key. These create an index as a side effect. Adding your own on the same column produces two structures doing one job. Employees.id is already indexed; nothing further is needed.