Skip to content
Tutorials Center
My learning

Topics

Lesson 31 of 37 ยท Performance and Patterns

Indexes and Reading Query Plans

How indexes speed up queries, how to read EXPLAIN output, which columns to index, composite index order and how to declare indexes in the ORMs.

An index is a sorted copy of one or more columns that lets the database jump straight to matching rows instead of reading the whole table. Without an index on email, finding one user among a million means a full table scan. With one, it is a handful of steps, like looking a word up in a dictionary instead of reading the book.

ORMs generate the SQL, but they cannot make it fast: only the schema can. In this lesson you will ask the database how it runs a query (EXPLAIN), see a scan turn into an index search, and learn the rules for choosing indexes. The demos use SQLite's EXPLAIN QUERY PLAN; PostgreSQL and MySQL have EXPLAIN and EXPLAIN ANALYZE with richer output but the same ideas.

Node.js Before and after an index

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

SCAN users means every row is read; SEARCH users USING INDEX means the database used the index. In PostgreSQL the words are Seq Scan and Index Scan.

Declaring indexes in the ORMs

TypeScript
// TypeORM
@Entity()
@Index(['country', 'createdAt'])            // composite
export class User {
  @Index({ unique: true })
  @Column() email!: string;
  @Column() country!: string;
  @Column() createdAt!: Date;
}
JavaScript
// Sequelize
const User = sequelize.define('User', { email: DataTypes.STRING, country: DataTypes.STRING }, {
  indexes: [
    { unique: true, fields: ['email'] },
    { fields: ['country', 'createdAt'] },
  ],
});

In a project with migrations, the index belongs in a migration too (createIndex / addIndex); see Safe Schema Changes for building one on a busy table.

Composite indexes: order matters

An index on (country, created_at) is sorted by country first, then by date within each country. It helps queries that filter on country, or on country and created_at, but not queries that filter only on created_at. The rule of thumb is the "leftmost prefix": equality columns first, then the range or sort column.

Node.js Column order in a composite index

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

The third query cannot use the index efficiently because it skips the leading column. (SQLite may still scan the index; the point is that it cannot jump to a position.)

What kills an index

Pattern Why the index is not used Fix
WHERE lower(email) = 'x' The function hides the column Index the expression, or store normalized data
WHERE name LIKE '%son' Leading wildcard cannot use a sorted order Full-text search or trigram index
WHERE id + 1 = 10 Arithmetic on the column Move arithmetic to the other side
Type mismatch (WHERE phone = 5551234 on a text column) Implicit cast Match the types
Low-selectivity column (active true/false) Index hardly narrows anything Combine it into a composite or a partial index
Node.js A function on the column defeats the index

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

The cost of indexes

Every index makes reads faster and writes slower, because each INSERT, UPDATE or DELETE must update every index, and indexes take disk and memory. Do not index everything. Index columns that appear in:

  • WHERE filters on large tables,
  • JOIN ... ON conditions (foreign keys are not automatically indexed in PostgreSQL),
  • ORDER BY combined with LIMIT,
  • uniqueness rules (UNIQUE).

Drop indexes that no query uses; PostgreSQL's pg_stat_user_indexes and MySQL's sys.schema_unused_indexes show them.

A workflow for slow queries

  1. Turn on ORM query logging and find the slow statement.
  2. Copy it into EXPLAIN (or EXPLAIN ANALYZE on PostgreSQL for real timings).
  3. Look for full scans on big tables, sorts on large sets and row estimates far from reality.
  4. Add the narrowest index that fits, then run EXPLAIN again.
  5. Check that writes did not suffer, and ship the index as a migration.

TypeORM lets you run raw SQL with dataSource.query('EXPLAIN ...'); Sequelize uses sequelize.query('EXPLAIN ...').

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does SCAN in a query plan usually mean?

    A scan reads the whole table; a search or index scan jumps to matching rows.
  2. 2. An index on (country, created_at). Which filter uses it best?

    The leftmost column must be constrained to jump into the index.
  3. 3. Why not index every column?

    Each write must update every index.
  4. 4. Which condition cannot use a plain index on email?

    Wrapping the column in a function hides it from the index unless you index that expression.

Summary

  • Indexes turn scans into searches; EXPLAIN shows which one you get.
  • Put equality columns first in composite indexes.
  • Functions, leading wildcards and type mismatches defeat indexes.
  • Indexes cost write speed and space, so index what queries really use.
  • Declare indexes in entities or models, and ship them in migrations.

Next: Pagination Patterns