1---
2title: Optimize Database Queries
3impact: HIGH
4impactDescription: Database queries are typically the largest source of latency
5tags: performance, database, queries, optimization
6---
7 
8## Optimize Database Queries
9 
10Select only needed columns, use proper indexes, avoid over-fetching relations, and consider query performance when designing your data access. Most API slowness traces back to inefficient database queries.
11 
12**Incorrect (over-fetching data and missing indexes):**
13 
14```typescript
15// Select everything when you need few fields
16@Injectable()
17export class UsersService {
18  async findAllEmails(): Promise<string[]> {
19    const users = await this.repo.find();
20    // Fetches ALL columns for ALL users
21    return users.map((u) => u.email);
22  }
23 
24  async getUserSummary(id: string): Promise<UserSummary> {
25    const user = await this.repo.findOne({
26      where: { id },
27      relations: ['posts', 'posts.comments', 'posts.comments.author', 'followers'],
28    });
29    // Over-fetches massive relation tree
30    return { name: user.name, postCount: user.posts.length };
31  }
32}
33 
34// No indexes on frequently queried columns
35@Entity()
36export class Order {
37  @Column()
38  userId: string; // No index - full table scan on every lookup
39 
40  @Column()
41  status: string; // No index - slow status filtering
42}
43```
44 
45**Correct (select only needed data with proper indexes):**
46 
47```typescript
48// Select only needed columns
49@Injectable()
50export class UsersService {
51  async findAllEmails(): Promise<string[]> {
52    const users = await this.repo.find({
53      select: ['email'], // Only fetch email column
54    });
55    return users.map((u) => u.email);
56  }
57 
58  // Use QueryBuilder for complex selections
59  async getUserSummary(id: string): Promise<UserSummary> {
60    return this.repo
61      .createQueryBuilder('user')
62      .select('user.name', 'name')
63      .addSelect('COUNT(post.id)', 'postCount')
64      .leftJoin('user.posts', 'post')
65      .where('user.id = :id', { id })
66      .groupBy('user.id')
67      .getRawOne();
68  }
69 
70  // Fetch relations only when needed
71  async getFullProfile(id: string): Promise<User> {
72    return this.repo.findOne({
73      where: { id },
74      relations: ['posts'], // Only immediate relation
75      select: {
76        id: true,
77        name: true,
78        email: true,
79        posts: {
80          id: true,
81          title: true,
82        },
83      },
84    });
85  }
86}
87 
88// Add indexes on frequently queried columns
89@Entity()
90@Index(['userId'])
91@Index(['status'])
92@Index(['createdAt'])
93@Index(['userId', 'status']) // Composite index for common query pattern
94export class Order {
95  @PrimaryGeneratedColumn('uuid')
96  id: string;
97 
98  @Column()
99  userId: string;
100 
101  @Column()
102  status: string;
103 
104  @CreateDateColumn()
105  createdAt: Date;
106}
107 
108// Always paginate large datasets
109@Injectable()
110export class OrdersService {
111  async findAll(page = 1, limit = 20): Promise<PaginatedResult<Order>> {
112    const [items, total] = await this.repo.findAndCount({
113      skip: (page - 1) * limit,
114      take: limit,
115      order: { createdAt: 'DESC' },
116    });
117 
118    return {
119      items,
120      meta: {
121        page,
122        limit,
123        total,
124        totalPages: Math.ceil(total / limit),
125      },
126    };
127  }
128}
129```
130 
131Reference: [TypeORM Query Builder](https://typeorm.io/select-query-builder)
132