Prisma•4w ago

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])
}

4 Replies

Prisma AI Help•4w ago

You selected to wait for the human sages. They'll share their wisdom soon. Grab some tea while you wait, or check out #ask-ai if you'd like a quick chat with the bot anyway!

Nurul•4w ago

Hey 👋 Is your History Collection very large? How much time is it currently taking? I see that you have already created indexes, which is good.

RezaNajafianOP•4w ago

It have 800K data and take time from 500ms to 10000ms i do second way of https://discord.com/channels/937751382725886062/1349713223557054476/1349713315915632694 Is it ok? :}

Nurul•3w ago

Yes that approach should also work 👍

Gaming

Programming

Optimizing MongoDB Query for Unique User Count

Did you find this page helpful?