CodingwithDom
Subscribe

What is a database index and when should I add one?

CS Fundamentals 2 min read
Short answer

An index is a sorted structure that lets the database find rows without scanning the whole table. Add one to columns you filter, join or sort by frequently.

sql
CREATE INDEX idx_videos_track ON videos(track);

Without it, WHERE track = 'python' reads every row. With it, the database jumps straight to the matching ones — the difference between O(n) and O(log n).

#Where indexes pay off

  • Columns in WHERE clauses
  • Foreign keys used in JOINs
  • Columns in ORDER BY
  • Columns with a UNIQUE constraint (which creates one automatically)

Primary keys are indexed for you.

#What they cost

Writes get slower. Every INSERT, UPDATE and DELETE must update every affected index.

They use disk. A large index on a large table is not free.

So do not index everything. Index what your slow queries actually filter on.

#Composite indexes and column order

sql
CREATE INDEX idx_track_views ON videos(track, views);

This serves WHERE track = ?, and WHERE track = ? AND views > ?. It does not help WHERE views > ? alone — an index can only be used from its leftmost column onward. Think of a phone book sorted by surname then first name: useless for finding everyone called Dominic.

#Things that stop an index being used

sql
WHERE LOWER(email) = 'a@b.com'      -- function on the column
WHERE views + 1 > 100               -- arithmetic on the column
WHERE title LIKE '%python%'         -- leading wildcard

Rewrite so the column stands alone, or create a functional index on the expression.

#Measure, do not guess

sql
EXPLAIN ANALYZE SELECT * FROM videos WHERE track = 'python';

If you see "Seq Scan" on a large table where you expected an index, the index is missing or unusable. This one command answers most database performance questions.

Related answers