RezaNajafian Posts - Answer Overflow

RezaNajafian

•Created by RezaNajafian on 3/13/2025 in #help-and-questions

Optimizing MongoDB Query for Unique User Count

How can I optimize my MongoDB query to count unique userIds for a specific action type (LOGIN_CLIENT) within a date range (startOfDayUTC to endOfDayUTC) without consuming too much time or resources?

const monthlyUniqueIDs = (await this.prismaService.history.findMany({
                    where: {
                        createdAt: {
                            gte: startOfMonthUTC,
                            lte: endOfMonthUTC,
                        },
                        actionType: "LOGIN_CLIENT"
                    },
                    distinct: ["userId"],
                    select: {
                        userId: true
                    }
                }));

                return monthlyUniqueIDs.length;

const monthlyUniqueIDs = (await this.prismaService.history.findMany({
                    where: {
                        createdAt: {
                            gte: startOfMonthUTC,
                            lte: endOfMonthUTC,
                        },
                        actionType: "LOGIN_CLIENT"
                    },
                    distinct: ["userId"],
                    select: {
                        userId: true
                    }
                }));

                return monthlyUniqueIDs.length;

model History {
  id String @id @default(auto()) @map("_id") @db.ObjectId

  actionType HistoryActionType

  ipAddress  String
  hardwareId String?

  user   User   @relation(fields: [userId], references: [id])
  userId String @db.ObjectId

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@index([userId, ipAddress, hardwareId, createdAt])
  @@index([actionType, createdAt])
}

model History {
  id String @id @default(auto()) @map("_id") @db.ObjectId

  actionType HistoryActionType

  ipAddress  String
  hardwareId String?

  user   User   @relation(fields: [userId], references: [id])
  userId String @db.ObjectId

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@index([userId, ipAddress, hardwareId, createdAt])
  @@index([actionType, createdAt])
}

8 replies

Gaming

Programming