Using indexes

Indexes are data structures that allow the database to locate rows without scanning the entire table. Without an index, a query must examine every row to find matches. With an index, the database can jump directly to the relevant rows — dramatically reducing the number of rows examined.

Indexes accelerate read operations but add overhead to writes (inserts, updates, deletes) since the index must be kept in sync, so they should be created carefully and only when needed.

Good candidates for indexes are columns frequently used in WHERE clauses, joins, and sorting. When more than one column is used as a filter, it might make sense to create a composite index, however bear in mind that column order matters — place the most selective (most unique values) columns first.

Analyzing query performance

Most databases let you inspect how a query is being executed, which is called an execution plan. Understanding such a plan helps you identify missing indexes and optimize how a query is constructed by comparing various ways of fetching the same data.

CockroachDB

Use EXPLAIN ANALYZE (same syntax as PostgreSQL):

EXPLAIN ANALYZE SELECT * FROM "user" WHERE "firstName" = 'Timber';

Look for full scan (no index) vs scan with a specific index name.

Learn more

• Indexes
• EXPLAIN

Google Spanner

Use the Query Plan visualizer in the Google Cloud Console, or run a query in PLAN/PROFILE mode via the gcloud CLI:

gcloud spanner databases execute-sql DATABASE_ID \
    --instance=INSTANCE_ID \
    --query-mode=PROFILE \
    --sql="SELECT * FROM user WHERE firstName = 'Timber'"

Look for Table Scan (no index) vs Index Scan in the returned plan.

Learn more

• Secondary Indexes
• Query Execution Plans

MariaDB

Use EXPLAIN or ANALYZE to inspect the query execution plan:

ANALYZE SELECT * FROM user WHERE firstName = 'Timber';

Check the type column — ALL means a full table scan, while ref, range, or index indicate index usage. The key column shows which index was chosen.

Learn more

• Optimization and Indexes
• EXPLAIN
• ANALYZE Statement

MongoDB

Use the explain() method on a query to see the execution plan:

db.user.find({ firstName: "Timber" }).explain("executionStats")

Look for COLLSCAN (collection scan — no index) vs IXSCAN (index scan).

Learn more

• Indexes
• Explain Results

MS SQL Server

Use the execution plan to analyze queries. In SQL Server Management Studio, press Ctrl+M to include the actual execution plan, then run your query. Programmatically:

SET STATISTICS IO ON;
SELECT * FROM [user] WHERE firstName = 'Timber';
SET STATISTICS IO OFF;

Look for Table Scan (no index) vs Index Seek or Index Scan in the execution plan.

Learn more

• Indexes
• Execution Plans

MySQL

Use EXPLAIN to inspect the query execution plan:

EXPLAIN SELECT * FROM user WHERE firstName = 'Timber';

Check the type column — ALL means a full table scan, while ref, range, or index indicate index usage. The key column shows which index was chosen.

Aurora MySQL

This also applies to Amazon Aurora MySQL, which uses the same query engine and EXPLAIN syntax. See Aurora MySQL tuning for Aurora-specific guidance.

Learn more

• Optimization and Indexes
• EXPLAIN Output Format

Oracle

Use EXPLAIN PLAN to inspect the execution plan:

EXPLAIN PLAN FOR SELECT * FROM "user" WHERE "firstName" = 'Timber';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Look for TABLE ACCESS FULL (no index) vs INDEX RANGE SCAN or INDEX UNIQUE SCAN.

Learn more

• Indexes and Index-Organized Tables
• Generating and Displaying Execution Plans

PostgreSQL

Use EXPLAIN ANALYZE to see how PostgreSQL executes a query and whether it uses indexes:

EXPLAIN ANALYZE SELECT * FROM "user" WHERE "firstName" = 'Timber';

Look for Seq Scan (full table scan — no index used) vs Index Scan or Index Only Scan (index used).

Aurora PostgreSQL

This also applies to Amazon Aurora PostgreSQL, which uses the same query engine and EXPLAIN syntax. Aurora also offers query plan management for capturing and controlling execution plans.

Learn more

• Indexes
• EXPLAIN
• Using EXPLAIN

SAP HANA

Use EXPLAIN PLAN FOR to inspect the execution plan:

EXPLAIN PLAN FOR SELECT * FROM "user" WHERE "firstName" = 'Timber';
SELECT * FROM EXPLAIN_PLAN_TABLE;

Look for TABLE SCAN (no index) vs INDEX SCAN or INDEX SEEK in the operator column.

Learn more

• Indexes
• EXPLAIN PLAN

SQLite

Use EXPLAIN QUERY PLAN to see how SQLite resolves a query:

EXPLAIN QUERY PLAN SELECT * FROM user WHERE firstName = 'Timber';

Look for SCAN (no index) vs SEARCH (index used) in the output.

Learn more

• Query Planning
• EXPLAIN QUERY PLAN

Defining indexes in TypeORM

TypeORM supports creating indexes on table columns using the @Index decorator.

Basic index

import { Entity, PrimaryGeneratedColumn, Column, Index } from "typeorm"

@Entity()
export class User {
    @PrimaryGeneratedColumn()
    id: number

    @Column()
    @Index()
    email: string

    @Column()
    firstName: string
}

Composite index

import { Entity, PrimaryGeneratedColumn, Column, Index } from "typeorm"

@Entity()
@Index(["lastName", "firstName"]) // most selective column first
export class User {
    @PrimaryGeneratedColumn()
    id: number

    @Column()
    firstName: string

    @Column()
    lastName: string
}

Join column

import {
    Entity,
    PrimaryGeneratedColumn,
    Column,
    ManyToOne,
    Index,
} from "typeorm"
import { User } from "./User"

@Entity()
export class Post {
    @PrimaryGeneratedColumn()
    id: number

    @Column()
    title: string

    @ManyToOne(() => User)
    @Index() // index foreign key columns to speed up joins
    author: User
}

For a comprehensive overview of different index types (unique, spatial, fulltext, concurrent, and more), read the Indexes guide.

Proper indexing is often the single biggest performance improvement you can make — start by analyzing your slowest queries with the execution plan tools above, then add indexes where they matter most.