When you write WHERE salary = 720000, how does the database find the matching rows? The naive answer is that it reads every row in employees, one at a time, and keeps the ones that pass — a full table scan. On fifteen rows that is instant. On fifteen million it is the difference between a snappy page and one that times out.
An index is a separate lookup structure the database builds and maintains so it can jump straight to the rows you want, the way a book's index jumps to a page instead of making you read every chapter. It trades a little storage and some upkeep for a dramatic cut in search time. Indexes are the single biggest lever for query performance, and unlike most optimisations they are a few lines of SQL to add or remove.
flowchart TD Q["need: rows WHERE salary = 720000"] Q --> A["no index: scan all 15 rows"] Q --> B["index on salary: jump to the match"] A --> AS["slow on big tables"] B --> BS["fast at any size"] style A fill:#b45309,color:#fff style B fill:#0e7490,color:#fff
Creating and inspecting an index
A simple index is one statement: CREATE INDEX idx_name ON table (column). The name is yours to choose (the idx_ prefix is a common habit), then the table and the column the index is built on. Run it once and the structure exists from then on.
Indexes live alongside your tables in a system catalogue called sqlite_master, so you can query them like any other data. SELECT name FROM sqlite_master WHERE type = 'index' lists every index in the database — including the automatic ones SQLite builds behind any PRIMARY KEY or UNIQUE constraint. Dropping an index is just as easy: DROP INDEX idx_name, and the lookup structure is gone while your table and its rows stay completely untouched.
CREATE INDEX idx_emp_salary ON employees (salary); SELECT name FROM sqlite_master WHERE type = 'index' AND name = 'idx_emp_salary';
The trade-off: faster reads, slower writes
An index is not free, and that cost is the heart of this lesson. Every time you INSERT, UPDATE, or DELETE a row, the database must update every index that covers the affected columns, so the structures stay correct. More indexes means more work on every write. Reads, meanwhile, get faster — sometimes dramatically.
So an index is a bet that a column will be searched far more often than it is written. A lookup table loaded once a day and queried a million times is a perfect candidate. A table that absorbs a constant firehose of inserts but is rarely filtered may be slowed for little benefit. There is no rule that says 'index everything'; the art is indexing the columns your queries actually filter, join, or sort on.
flowchart LR I["add an index"] --> R["reads get<br/>FASTER"] I --> W["writes get<br/>SLOWER"] style R fill:#0e7490,color:#fff style W fill:#b45309,color:#fff
-- without an index this would SCAN the whole table; with one it SEARCHes: CREATE INDEX idx_emp_salary ON employees (salary); EXPLAIN QUERY PLAN SELECT name FROM employees WHERE salary = 720000;
Choosing what to index
The columns worth indexing are the ones your queries use to narrow the result — the columns in WHERE, JOIN ... ON, and ORDER BY. A column that picks out a tiny slice of the table, like an id or a rare status, has high selectivity and rewards an index richly. A column that returns almost every row when filtered, like a boolean flag that is true 99% of the time, has low selectivity; the index barely helps because the database still reads most of the table.
A useful mental test: after applying the filter, how few rows remain? If the answer is a handful, an index shines. If it is almost the whole table, save the write overhead and skip the index.
flowchart TD
G{"filter narrows rows<br/>to a small set?"}
G -->|"yes: high selectivity"| Y["index helps a lot"]
G -->|"no: returns most rows"| N["index barely helps"]
style Y fill:#0e7490,color:#fff
style N fill:#b45309,color:#fff
Multi-column (composite) indexes
An index can cover more than one column: CREATE INDEX idx_emp_dept_pay ON employees (department_id, salary). This is one structure sorted first by department_id, then by salary within each department. It speeds up any query that filters on department_id alone, and even more so one that filters on both columns at once.
The order of columns inside a composite index matters, because the index is useful starting from the left. A query filtering only salary cannot use this index efficiently, since the salaries are grouped inside departments rather than held in global order. The rule is to put the column you filter on most — or the one with the highest selectivity — first, and let the rest follow in order of use.
CREATE INDEX idx_emp_dept_pay ON employees (department_id, salary); SELECT department_id, name, salary FROM employees WHERE department_id = 1 AND salary > 700000 ORDER BY salary DESC; DROP INDEX idx_emp_dept_pay; SELECT COUNT(*) AS indexes_left FROM sqlite_master WHERE type = 'index';
Create an index called idx_emp_salary on the salary column of employees.
CREATE INDEX idx_emp_salary ;
The shape is CREATE INDEX name ON table (column).
The table is employees and the column is salary.
Wrap the column in parentheses.
CREATE INDEX idx_emp_salary ON employees (salary);
What is the main cost of adding an index to a table?
An index is a second structure maintained on every write, so reads speed up but writes slow down. That maintenance cost is the core trade-off.
Queries filter employees by department and then by pay. Create a composite index named idx_emp_dept_sal covering department_id first and salary second.
CREATE INDEX idx_emp_dept_sal ;
List both columns inside the parentheses, separated by a comma.
Order matters: department_id first, then salary.
The table is employees.
CREATE INDEX idx_emp_dept_sal ON employees (department_id, salary);
You can index one column to speed up lookups. Which makes the most useful index?
An index helps most when it narrows the table to a small set of rows — high selectivity. A unique id is maximally selective; a constant column narrows nothing.
A common report filters projects by status. Create an index named idx_proj_status on the status column of projects so those lookups can skip the full scan.
CREATE INDEX idx_proj_status ;
The pattern is CREATE INDEX name ON table (column).
The table is projects and the column is status.
Pick the column the report filters on.
CREATE INDEX idx_proj_status ON projects (status);
Recap
- Without an index,
WHEREdoes a full table scan, reading every row; an index lets the database jump to the matching ones. - Create with
CREATE INDEX name ON table (column), drop withDROP INDEX name; both leave the table's data untouched. - Indexes live in
sqlite_masteralongside tables, andPRIMARY KEYandUNIQUEcolumns already get automatic indexes. - The trade-off is faster reads for slower writes — every insert, update, and delete must maintain each index.
- Index the columns your queries filter, join, or sort on, especially high-selectivity ones, and order composite indexes from the most selective column leftward.
Next you will save whole queries as reusable views, and then evolve these tables safely with migrations.