An index is a bet that a column will be searched far more often than it changes. A bad bet wastes disk space, slows every write, and may never be used at all. The difference between a helpful index and a useless one is not the syntax — it is the strategy behind the choice.
This lesson covers three strategic ideas. Selectivity tells you whether an index will narrow the table enough to matter. A covering index contains every column a query needs, so the engine never touches the table itself. A composite index spans multiple columns, but only helps queries that match its leftmost prefix. Master these three and you will stop guessing and start designing.
flowchart TD
Q["query filters on a column?"] -->|"yes"| S{"high selectivity?<br/>narrows to few rows"}
S -->|"yes"| C{"all needed columns<br/>in the index?"}
C -->|"yes"| CO["covering index"]
C -->|"no"| I["standard index"]
S -->|"no"| N["probably skip it"]
style CO fill:#0e7490,color:#fff
style N fill:#b45309,color:#fff
Selectivity: will the index narrow the table?
A column with mostly unique values — like an email address or a timestamp — has high selectivity. An index on it jumps to a tiny slice of the table and pays off immediately. A column with the same value on every row has zero selectivity; the index points at everything, so the engine might as well scan.
The mental test is simple: after applying the filter, how many rows remain? If the answer is a small handful, the index is a strong candidate. If the answer is most of the table, skip the index and let the scan do its job. Boolean flags, status columns with only two values, and foreign keys to a tiny lookup table are often low-selectivity and poor index choices on their own.
SELECT name, salary FROM employees WHERE salary > 900000;
Reading the plan for selectivity
You can estimate selectivity by running the query and counting the result. On our seed data, WHERE salary > 900000 returns two rows out of fifteen. That is highly selective, so an index on salary would be a clear win. Contrast that with WHERE department_id IS NOT NULL, which returns fourteen rows. An index there would barely remove any work, and the planner would probably ignore it in favor of a scan.
The same logic applies to joins. A join from employees to departments returns one department per employee. An index on employees.department_id is selective because each value points to a small set of rows. Without it, the join becomes a scan paired with a scan — a Cartesian product in disguise.
Covering indexes: answer the query from the index alone
When a query needs only the columns that are in the index, SQLite can satisfy the request without ever reading the actual table row. This is called a covering index, and it is the fastest possible lookup because the index is smaller and more cache-friendly than the full table.
For example, an index on employees(department_id, name) covers the query SELECT name FROM employees WHERE department_id = 1. The engine walks the index to the rows for department 1 and finds the names right there. It never needs to look up the full employee record. Add more columns to the index than the query needs, and you risk bloating the index without gaining coverage.
flowchart LR Q["SELECT name FROM employees WHERE department_id = 1"] --> I["index on (department_id, name)"] I -->|"contains filter<br/>and output"| R["result"] T["employees table"] -.->|"not touched"| R style I fill:#0e7490,color:#fff style T fill:#b45309,color:#fff
CREATE INDEX idx_emp_dept_name ON employees (department_id, name); SELECT name FROM employees WHERE department_id = 1;
Composite indexes and the leftmost prefix
An index can span multiple columns: CREATE INDEX idx ON employees (department_id, salary, name). The database sorts the index first by department_id, then by salary within each department, then by name. This single structure can serve queries that filter on department_id alone, on department_id and salary, or on all three. But it cannot efficiently help a query that filters only on salary or only on name, because those columns are not at the left edge of the sort order.
The rule is called the leftmost-prefix rule: a composite index is usable starting from its first column and stopping anywhere to the right, but you cannot skip the leftmost column and still use the index. When you design a composite index, lead with the column that appears most often in your WHERE clauses, especially the one with the highest selectivity.
flowchart TD I["index on (dept, sal, name)"] --> Q1["WHERE dept = 1"] I --> Q2["WHERE dept = 1 AND sal > 700000"] I --> Q3["WHERE dept = 1 AND sal > 700000 AND name = 'Ada'"] I -.->|"cannot use index"| Q4["WHERE sal > 700000"] style Q1 fill:#0e7490,color:#fff style Q2 fill:#0e7490,color:#fff style Q3 fill:#0e7490,color:#fff style Q4 fill:#b45309,color:#fff
Ordering columns inside a composite index
The first column in a composite index should be the one that appears most often in your WHERE clauses, especially if it has high selectivity. The second column should be the next most common filter, or a column you frequently sort by. Columns that are only selected but never filtered belong at the end, or not in the index at all, because they do not help the engine narrow the search.
Resist the temptation to lead with a low-selectivity column. A composite index on (status, department_id) is less useful than one on (department_id, status) if most queries filter by department first. The leftmost column is the entry point; choose the one that eliminates the most rows up front.
CREATE INDEX idx_emp_dept_sal ON employees (department_id, salary); SELECT name, salary FROM employees WHERE department_id = 1 AND salary > 700000; DROP INDEX idx_emp_dept_sal;
Create a covering index named idx_emp_role_name on employees(role, name) so that a query like SELECT name FROM employees WHERE role = 'Engineer' can be answered without touching the table.
CREATE INDEX idx_emp_role_name ;
List both columns inside the parentheses, separated by a comma.
Role first, then name.
CREATE INDEX idx_emp_role_name ON employees (role, name);
A report always filters projects by department_id and then by status. Create a composite index named idx_proj_dept_status on projects that matches this pattern.
CREATE INDEX idx_proj_dept_status ;
Put department_id first because it is the broader filter.
Add status as the second column.
CREATE INDEX idx_proj_dept_status ON projects (department_id, status);
A junior developer created an index with the wrong leftmost column. Drop idx_bad if it exists and create the correct index idx_emp_dept on employees(department_id).
DROP INDEX IF EXISTS idx_bad; CREATE INDEX idx_emp_dept ;
DROP INDEX IF EXISTS idx_bad removes the bad index safely.
Create the new index with department_id as the only column.
DROP INDEX IF EXISTS idx_bad; CREATE INDEX idx_emp_dept ON employees (department_id);
You have a composite index CREATE INDEX idx ON employees (department_id, salary, name). Which query CANNOT use this index efficiently?
A composite index works from the leftmost column. The third query skips department_id, so the engine cannot use the index tree and falls back to a scan.
Write a query that returns the name and salary of every employee in the Engineering department (department_id = 1), ordered by salary from highest to lowest. Do not use SELECT *.
SELECT -- name and salary of Engineering employees FROM employees -- filter and sort ;
Filter with WHERE department_id = 1.
Order by salary DESC for highest first.
Select only name and salary.
SELECT name, salary FROM employees WHERE department_id = 1 ORDER BY salary DESC;
Recap
- Selectivity decides whether an index is worth building: high-selectivity columns narrow the table to a handful of rows.
- A covering index contains every column a query needs, so the engine never reads the table itself.
- Composite indexes serve queries that match their leftmost prefix; order columns from most-used to least-used.
- Multiple single-column indexes often lose to one well-designed composite index.
- Always let your real query patterns dictate what you index, rather than indexing every column by reflex.
Next you will learn to spot anti-patterns in query design and rewrite them into faster, clearer SQL.