Lesson 17 of 20

In progress

Course progress

SQL

Indexes: making lookups fast

Understand what an index stores, choose the right columns and order, read a query plan, and know when an index is wasted.

Without an index, finding a row means reading the whole table. With one, the engine jumps to the right place. That is the whole idea, and the craft is in choosing which columns.

What you will learn

  • Read an index as a sorted lookup structure.
  • Order composite indexes to match queries.
  • Check a plan and decide whether an index helps.

Seeing the difference

SQL
Editable. Runs in your browser.





Press Run to see the output.

The plan says SEARCH events USING INDEX idx_events_kind (kind=?), which means the engine used the index instead of scanning. Without the index it would say SCAN events. Read plans with EXPLAIN QUERY PLAN in SQLite; other databases have EXPLAIN with their own output.

The plan is the only reliable answer to "is this index used". Guessing from the query shape is how people add indexes that never run.

What an index stores

An index is a sorted structure of the indexed columns plus a pointer to the row. Because it is sorted, the engine can binary-search it, and it can also read a range in order without a sort step.

That explains three things:

  • Equality on the indexed column is fast.
  • A range or ORDER BY on it can avoid a sort.
  • An index on a column you never filter or sort by does nothing for reads.

Composite indexes and column order

SQL
Editable. Runs in your browser.





Press Run to see the output.

A composite index is sorted by the first column, then the second within equal first values. That means:

  • A query filtering on actor alone can use it.
  • A query filtering on created_on alone cannot, because the first column is not constrained.
  • A query filtering on both uses both parts.

Order the columns by how they are queried: equality columns first, then the range or sort column. The rule of thumb is "equality, then range, then everything you select".

Covering indexes

If the index contains every column a query needs, the engine can answer from the index and never touch the table.

SQL
Editable. Runs in your browser.





Press Run to see the output.

The plan may mention USING COVERING INDEX, which is the fastest case for a read-heavy report. The cost is a wider index, so cover the queries that matter rather than every query.

When indexes hurt

Every insert, update and delete must also update each index. A table with eight indexes writes nine structures per row. On a write-heavy table that is a real cost, and it is invisible until the write volume grows.

Indexes also take space and slow down bulk imports. A common pattern is to drop non-essential indexes before a large import and recreate them afterwards.

Low-selectivity columns are poor candidates on their own: an index on a boolean flag divides the table in two, and a scan of half the rows is barely better than a full scan. A composite index that starts with the flag and continues with a selective column is often useful instead.

What defeats an index

  • WHERE LOWER(name) = 'ana' unless there is an index on the expression.
  • WHERE name LIKE '%ana', because the match can start anywhere.
  • WHERE CAST(id AS TEXT) = '42', because the indexed value is not being compared.
  • A type mismatch that forces conversion, such as comparing a text column with a number.

The fix is usually to change the query rather than the index: store the value normalised, or add an expression index when the database supports one.

Indexes and uniqueness

A UNIQUE constraint creates an index automatically, which is why lookups by email are fast without an extra CREATE INDEX. The same is true for primary keys. Do not duplicate those indexes.

Adding an index for every column that appears in a WHERE clause. Each one slows writes, most go unused, and the plan for a multi-condition query may need a composite index instead.

Add an index that supports finding an actor’s events in a date range, then check the plan for a query using both conditions and one using only the date.

Show solution
SQL
Editable. Runs in your browser.





Press Run to see the output.

The first plan searches the index, because the leading column is constrained. The second scans, because created_on is the second column and cannot be used without a value for actor. A separate index on created_on would be needed for that query, and whether it is worth the write cost depends on how often it runs.

Recap

  • An index is a sorted structure that turns a scan into a search, and it is only used when the query matches its shape.
  • Composite indexes are ordered: equality columns first, then ranges and sorts.
  • Every index costs write time and space, and expressions or leading wildcards can defeat it.

Next, transactions: grouping changes so a failure cannot leave half the work done.

How many indexes is too many?
There is no number. Add one for a query that is measurably slow, and remove ones the plan never uses.
Do indexes help COUNT(*)?
They can make it cheaper by scanning a smaller structure, but a full count still touches every row in most engines.

Progress is saved in this browser only. No account, no email.

Admin

About the author

Admin

Instructor and technical writer at tutorials.in

Writes beginner lessons for tutorials.in. Each lesson is checked against the language or tool documentation, and examples are run before publishing.

Reviewed by the editorial team before publishing. Last updated November 16, 2026.