Himanshu SharmaSoftware engineer
Let’s talk

SQL · Lesson 46 of 49

Indexes: why some queries are fast and others are slow

Understand what a database index is, create one with CREATE INDEX, read a query plan, and learn when an index helps, when it is ignored, and what it costs.

16 minDesigning databasesUpdated 14 November 2026

Awaiting technical review. This lesson is readable but not yet listed in search engines.

By the end of this lessonCreate an index for a slow lookup, check whether the database uses it, and explain the trade-off against slower writes.

Finding a row without reading everything

Suppose you want the page about "recursion" in a thick book. You would not read from page one. You would open the index at the back, find the word, and jump to the page number. A database index does the same job for a table.

Without an index, a query like WHERE last_name = 'Rao' has to read every row and check each one, which is called a full table scan. On twenty rows that is instant. On twenty million it is painful. After this lesson you will be able to create an index, check whether a query actually uses it, and weigh up what it costs.

The examples use the sample company database in SQLite 3.54. That database is tiny, so you will not feel a speed difference, and this lesson does not claim any timings. Instead it shows the query plan, the database's description of how it will run the statement, which is where the difference is visible.

Creating an index

sql
CREATE INDEX idx_employees_last_name ON employees (last_name);

The name comes first, then the table, then the column or columns in parentheses. The index is created once, and the database keeps it up to date from then on. Queries do not mention it, and you never write FROM idx_.... The database decides when to use it.

Reading a query plan

To see the plan, put a command in front of the query. In SQLite it is EXPLAIN QUERY PLAN. Before the index exists:

sql
EXPLAIN QUERY PLAN SELECT * FROM employees WHERE last_name = 'Rao';
result
QUERY PLAN
`--SCAN employees

SCAN means the whole table is read. After CREATE INDEX, the same command gives:

result
QUERY PLAN
`--SEARCH employees USING INDEX idx_employees_last_name (last_name=?)

Now the word is SEARCH, and the plan names the index and the condition it uses. The query itself, and its answer, did not change:

sql
SELECT first_name, last_name FROM employees WHERE last_name = 'Rao';
result
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Arjun      | Rao       |
+------------+-----------+

Treat this output as an illustration, not as a format to memorise. Plans are not part of standard SQL, and every database has its own way to ask for one and its own wording. MySQL and PostgreSQL use EXPLAIN (and PostgreSQL adds EXPLAIN ANALYZE, which really runs the query), while SQL Server shows an execution plan from its tools or through SET SHOWPLAN options. The ideas carry over even though the text does not. The MySQL EXPLAIN lesson reads MySQL's version in detail, and the MySQL indexes lesson covers its index types.

Indexes you already have

You have been using indexes without writing them. A primary key is indexed, and so is every UNIQUE column, because the database needs a fast way to check for duplicates. Looking up by the key or by the unique email uses them:

sql
EXPLAIN QUERY PLAN SELECT * FROM employees WHERE id = 7;
EXPLAIN QUERY PLAN SELECT * FROM employees WHERE email = 'neha.joshi@example.com';
result
QUERY PLAN
`--SEARCH employees USING INTEGER PRIMARY KEY (rowid=?)
QUERY PLAN
`--SEARCH employees USING INDEX sqlite_autoindex_employees_1 (email=?)

SQLite names the automatic index itself. In SQLite the INTEGER PRIMARY KEY column is a special case, because it is the row's own identifier and needs no separate index. Foreign key columns are not always indexed for you (PostgreSQL and SQLite do not do it, while MySQL's InnoDB engine does), even though joins and deletes on them benefit, so check before assuming. In the sample database, for example, assignments has a primary key on (employee_id, project_id). A search by employee_id uses it, but a search by project_id alone is a scan:

sql
EXPLAIN QUERY PLAN SELECT * FROM assignments WHERE project_id = 2;
CREATE INDEX idx_assignments_project ON assignments (project_id);
EXPLAIN QUERY PLAN SELECT * FROM assignments WHERE project_id = 2;
result
QUERY PLAN
`--SCAN assignments
QUERY PLAN
`--SEARCH assignments USING INDEX idx_assignments_project (project_id=?)

Composite indexes

An index can cover several columns. The order matters, because the index is sorted by the first column, then by the second within each first value, like a phone book sorted by surname and then by first name.

sql
CREATE INDEX idx_emp_dept_salary ON employees (department_id, salary);

EXPLAIN QUERY PLAN SELECT * FROM employees WHERE department_id = 1 AND salary > 100000;
EXPLAIN QUERY PLAN SELECT * FROM employees WHERE salary > 100000;
result
QUERY PLAN
`--SEARCH employees USING INDEX idx_emp_dept_salary (department_id=? AND salary>?)
QUERY PLAN
`--SCAN employees

The first query uses both columns. The second asks only about salary, the second column, and the index is not much help because the leading column is missing. A phone book is no use if you know a first name but not a surname. As a rule, put the column you test with = first.

An index can also help with sorting. Without one, ORDER BY salary for a department needs a separate sorting step. With the composite index, the rows come out already in order:

sql
EXPLAIN QUERY PLAN SELECT * FROM employees WHERE department_id = 1 ORDER BY salary;
result
QUERY PLAN
`--SEARCH employees USING INDEX idx_emp_dept_salary (department_id=?)

Without that index, SQLite shows SCAN employees followed by USE TEMP B-TREE FOR ORDER BY for the same query. The second line is the separate sorting step.

When an index is ignored

An index can only help when the query is written to match it. Two common ways to lose it:

  • Wrapping the column in a function. WHERE UPPER(last_name) = 'RAO' compares uppercased values, which the index does not hold, so the plan falls back to SCAN employees.
  • A leading wildcard. WHERE last_name LIKE '%ao' cannot jump to a starting point, because the match could begin anywhere. A pattern anchored at the start, such as 'Ra%', can use an index in many databases. Whether it does depends on the database and its text settings, so check the plan.

Some databases allow an index on an expression, so that UPPER(last_name) is itself indexed. SQLite, PostgreSQL and recent MySQL versions have this in some form, and the spelling differs.

The costs

Indexes are not free:

  • Slower writes. Every insert, update and delete must also update each index on the table.
  • Space. Each index is stored on disk, sometimes as large as the table.
  • Planner confusion. Too many overlapping indexes make it harder to see which one matters.

So index for the queries you actually run: columns in frequent WHERE filters, join conditions and ORDER BY. A column with only a handful of distinct values, such as a yes or no flag, usually gains little. Check your plans before and after, and remove indexes nobody uses.

Dropping an index

DROP INDEX removes it. SQLite and PostgreSQL write DROP INDEX name;, whereas MySQL and SQL Server need the table too: DROP INDEX name ON table;.

sql
DROP INDEX idx_emp_dept_salary;

Dropping an index never changes your data or the query results, only how fast they arrive.

Common mistakes

  • Indexing every column. It slows every write and rarely helps queries you do not run.
  • Putting the columns of a composite index in the wrong order. Lead with the column used for equality filters.
  • Applying a function to an indexed column in WHERE and wondering why the plan scans.
  • Trusting a guess. Look at the plan, and on a realistic amount of data, because a tiny table says little about a large one.
  • Assuming foreign key columns are indexed. In PostgreSQL and SQLite they are not, which can make joins and parent deletes slow on big tables.

Exercise

On the sample database, projects has a department_id column but no index on it. Look at the plan for finding the projects of department 1, create an index on that column, and look at the plan again.

Show one possible answer
sql
EXPLAIN QUERY PLAN SELECT * FROM projects WHERE department_id = 1;

CREATE INDEX idx_projects_department ON projects (department_id);

EXPLAIN QUERY PLAN SELECT * FROM projects WHERE department_id = 1;
result
QUERY PLAN
`--SCAN projects
QUERY PLAN
`--SEARCH projects USING INDEX idx_projects_department (department_id=?)

The first plan is a SCAN, and the second is a SEARCH that names the new index. The answer to the query itself does not change.

Key points

  • An index is a separate sorted structure that lets the database jump to matching rows instead of reading the whole table.
  • Primary keys and UNIQUE constraints already create indexes. Add your own for columns you often filter, join or sort on.
  • A composite index on (a, b) helps queries that use a, or a and b, but generally not b alone.
  • Indexes cost disk space and slow down inserts, updates and deletes, so add them for real queries, not for every column.
  • EXPLAIN shows the plan, but its command and output look different in every database.

Check yourself

1. What does an index do?
2. A table has an index on (department_id, salary). Which query can use it most effectively?
3. What is the main cost of adding many indexes to a table?
4. Why does WHERE UPPER(last_name) = 'RAO' usually ignore an ordinary index on last_name?
5. Is the EXPLAIN QUERY PLAN command available in every database?