Designing Indexes

Knowing what an index is does not tell you which ones to create. That is a design problem, and it is mostly about column order.

Composite indexes

An index can cover several columns:

CREATE INDEX idx_employees_dept_salary ON employees (dept_id, salary);

This is not the same as two separate indexes. It is one structure sorted by dept_id first, then by salary within each department — a phone book ordered by surname, then first name.

The leftmost-prefix rule

That ordering decides what the index can serve. An index on (a, b, c) helps queries filtering on:

• a
• a and b
• a, b and c

and does not help queries filtering only on b, only on c, or on b and c together — just as a phone book sorted by surname cannot find everyone called Sarah without reading all of it.

So for (dept_id, salary):

That last row matters: the order of conditions in your WHERE clause has no effect. Only the order of columns in the index definition does.

Choosing the order

Two rules, in this order of priority:

1. Equality columns before range columns. In WHERE dept_id = 1 AND salary > 80000, put dept_id first. Once the index hits a range condition it can no longer use later columns for seeking — it must scan from that point. (dept_id, salary) is right; (salary, dept_id) forces a scan of everyone above 80,000 in every department.
2. More selective columns first, among equality columns. A column that narrows to a handful of rows eliminates more work earlier.

Then check the real queries. An index designed from first principles that no query matches is pure cost.

Covering indexes

If an index contains every column a query needs, the database never reads the table at all — an index-only scan, the fastest outcome available:

CREATE INDEX idx_emp_covering ON employees (dept_id, salary, first_name);

Now SELECT first_name, salary FROM employees WHERE dept_id = 1 is answered entirely from the index. PostgreSQL and SQL Server support INCLUDE for this, adding columns to the leaf level without making them part of the sort key:

CREATE INDEX idx_emp_covering ON employees (dept_id) INCLUDE (salary, first_name);

The trade-off: a wider index takes more space and more work to maintain. Covering is for hot queries, not everything.

Indexes for sorting

An index can remove a sort entirely if its order matches the ORDER BY. Lesson 34 showed USE TEMP B-TREE FOR ORDER BY on ORDER BY salary DESC; an index on salary lets the database read in order instead.

For this to work the directions must be compatible. An index on (dept_id, salary) serves ORDER BY dept_id, salary and also ORDER BY dept_id DESC, salary DESC (read backwards), but not ORDER BY dept_id, salary DESC — mixed directions need an index declared that way: (dept_id ASC, salary DESC).

This is where sorting and pagination meet. Lesson 09's keyset pagination is fast precisely because an index provides both the filter and the order.

Partial and functional indexes

Partial — index only the rows you query (PostgreSQL, SQLite, SQL Server as "filtered"):

CREATE INDEX idx_active_employees ON employees (dept_id) WHERE active = 1;

Smaller, cheaper to maintain, and ideal for the common case of "99% of queries only look at active rows".

Functional — index the result of an expression:

CREATE INDEX idx_customers_lower_name ON customers (LOWER(name));

This is what makes WHERE LOWER(name) = 'acme ltd' fast, and it is the answer to the problem the next lesson is about. PostgreSQL and Oracle support these directly; MySQL 8.0 has functional indexes; SQL Server needs a computed column indexed instead.

Which indexes to create

A practical procedure:

1. Start with primary keys and unique constraints — you already have them.
2. Index every foreign key. Outside MySQL these are not automatic, and they are the most common missing index in any schema.
3. Look at the slow query log. Index what is actually slow, not what looks slow.
4. For each slow query, read the plan and design the composite index its WHERE and ORDER BY need.
5. Measure. Keep it if the plan improved.
6. Periodically drop unused indexes — every database can report which ones have never been read.

Signs you have too many

• Writes have slowed noticeably.
• Several indexes share a leftmost prefix — (a), (a, b) and (a, b, c) where (a, b, c) alone serves all three.
• Index storage rivals table storage.
• Nobody can say which query a given index exists for.

That second point is the easiest win in most schemas: a redundant (a) alongside (a, b) can usually just be dropped.

Common mistakes

• Assuming WHERE order matters — only the index definition's order does.
• Range column first in a composite — it blocks the columns after it.
• One index per column instead of one composite index — the database can rarely combine them as well.
• Never dropping anything — indexes accumulate and only cost.

Interview question

You have an index on (a, b, c). Which of these queries can use it: WHERE a = 1, WHERE b = 2, WHERE a = 1 AND c = 3?

The first fully. The second not at all — b is not a leftmost prefix. The third uses the index for a, then filters on c afterwards, because b is missing from the middle. Explaining it as a phone book usually makes the point faster than the terminology.

Check yourself

1. Why should equality columns come before range columns?
2. What is a covering index and why is it fast?
3. Why might (a) be redundant when (a, b) exists?