NodeJS
A Comprehensive Guide to Using Composite Indexes in TypeORM
Introduction
In TypeORM, a composite index utilizes multiple columns in a single index. This type of index increases efficiency in situations where there are filters or sorts involving more than one column. The performance of composite indexes is better because there are fewer rows scanned in the database when performing the search.
Why Use Composite Indexes?
- Filtering by different columns greatly enhances performance.
- There is better efficiency in sorting with multiple columns.
- There is no longer a need for multiple single-column indexes.
Defining a Composite Index in TypeORM
In order to create a composite index, you need to use the @Index decorator in an entity class on TypeORM.
Example: Defining a Composite Index
import { Entity, PrimaryGeneratedColumn, Column, Index } from 'typeorm';@Entity()@Index(['firstName', 'lastName'])class User { @PrimaryGeneratedColumn() id: number; @Column() firstName: string; @Column() lastName: string; @Column() age: number;}
An index for both firstName and lastName will be created.
Using Unique Composite Index
When a unique composite index is specified, a set of values from the specified columns will be unique by default.
@Entity()@Index(['email', 'phoneNumber'], { unique: true })class Contact { @PrimaryGeneratedColumn() id: number; @Column() email: string; @Column() phoneNumber: string;}
This ensures that the rows with an email and phoneNumber combination will always be unique.
Using Composite Indexes for Better Queries
Queries that filter by indexed composite columns are the ones that benefit the most.
const users = await userRepository.find({ where: { firstName: 'John', lastName: 'Doe' } });
The database will utilize the composite index to return results with great speed, as it has the index.
Things to Keep Into Account for Composite Indexes
- Indexes are sensitive to the order of columns defined, therefore @Index(['A', 'B']) is not equal to @Index(['B', 'A']).
- Composite indexes should only be added to columns that are often provided as filters in queries or are sorted using multiple columns.
- Stay clear of creating multiple indexes, as this expenses space and slows down write guidelines.
Conclusion
Composite indexes in TypeORM boost up queries that use many columns to filter or sort data. Ensuring data accuracy while increasing database speed are made easier with the appropriate indexing approach.
Ready to transform your business with our technology solutions? Contact Us today to Leverage Our NodeJS Expertise.
NodeJS