# 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](https://typeorm.io/docs/indexes.md#indexes-with-multiple-columns), however bear in mind that column order matters — place the most selective (most unique values) columns first.

## Analyzing query performance[​](#analyzing-query-performance "Direct link to 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](/img/databases/cockroachdb.svg "CockroachDB")
* ![Google Spanner](/img/databases/spanner.svg "Google Spanner")
* ![MariaDB](/img/databases/mariadb.svg "MariaDB")
* ![MongoDB](/img/databases/mongodb.svg "MongoDB")
* ![MS SQL Server](/img/databases/mssql.svg "MS SQL Server")
* ![MySQL](/img/databases/mysql.svg "MySQL")
* ![Oracle](/img/databases/oracle.svg "Oracle")
* ![PostgreSQL](/img/databases/postgresql.svg "PostgreSQL")
* ![SAP HANA](/img/databases/sap.svg "SAP HANA")
* ![SQLite](/img/databases/sqlite.svg "SQLite")

### 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](https://www.cockroachlabs.com/docs/stable/indexes.html)
* [EXPLAIN](https://www.cockroachlabs.com/docs/stable/explain.html)

### Google Spanner

Use the [Query Plan visualizer](https://cloud.google.com/spanner/docs/query-execution-plans#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](https://cloud.google.com/spanner/docs/secondary-indexes)
* [Query Execution Plans](https://cloud.google.com/spanner/docs/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](https://mariadb.com/kb/en/optimization-and-indexes/)
* [EXPLAIN](https://mariadb.com/kb/en/explain/)
* [ANALYZE Statement](https://mariadb.com/kb/en/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](https://www.mongodb.com/docs/manual/indexes/)
* [Explain Results](https://www.mongodb.com/docs/manual/reference/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](https://learn.microsoft.com/en-us/sql/relational-databases/indexes/indexes)
* [Execution Plans](https://learn.microsoft.com/en-us/sql/relational-databases/performance/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](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Managing.Tuning.html) for Aurora-specific guidance.

Learn more

* [Optimization and Indexes](https://dev.mysql.com/doc/refman/en/optimization-indexes.html)
* [EXPLAIN Output Format](https://dev.mysql.com/doc/refman/en/explain-output.html)

### 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](https://docs.oracle.com/en/database/oracle/oracle-database/23/cncpt/indexes-and-index-organized-tables.html)
* [Generating and Displaying Execution Plans](https://docs.oracle.com/en/database/oracle/oracle-database/23/tgsql/generating-and-displaying-execution-plans.html)

### 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](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Optimize.html) for capturing and controlling execution plans.

Learn more

* [Indexes](https://www.postgresql.org/docs/current/indexes.html)
* [EXPLAIN](https://www.postgresql.org/docs/current/sql-explain.html)
* [Using EXPLAIN](https://www.postgresql.org/docs/current/using-explain.html)

### 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](https://help.sap.com/docs/hana-cloud-database/sap-hana-cloud-sap-hana-database-sql-reference-guide/indexes)
* [EXPLAIN PLAN](https://help.sap.com/docs/hana-cloud-database/sap-hana-cloud-sap-hana-database-sql-reference-guide/explain-plan-statement-data-manipulation)

### 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](https://www.sqlite.org/queryplanner.html)
* [EXPLAIN QUERY PLAN](https://www.sqlite.org/eqp.html)

## Defining indexes in TypeORM[​](#defining-indexes-in-typeorm "Direct link to Defining indexes in TypeORM")

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

* Basic index
* Composite index
* Join column

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



@Entity()

export class User {

    @PrimaryGeneratedColumn()

    id: number



    @Column()

    @Index()

    email: string



    @Column()

    firstName: string

}
```

```
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

}
```

```
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](https://typeorm.io/docs/indexes.md) 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.