javascriptprisma

Question about Prisma indexes, response its slow


I just noticed that I haven an issue with one of my queries. I have the following model on prisma: Database: MYSQL

model Subscriber {
  id         String        @id @default(uuid())
  timestamp  DateTime      @default(now())
  popup_id   String
  user_id    String
  session_id String?
  popup_type PopupTypeEnum
  user_agent String?
  referrer   String?
  url        String
  email      String
  code       String?

  @@index([user_id, timestamp(sort: Desc)])
}

The issue is that when I fetch a response with the orderBy its taking 1 min to answer. And if I remove the orderBy its instant. The db has aprox 200k rows because im testing.

const subscribers = await prisma.subscriber.findMany({
        where: whereClause,
        take: limit,
        orderBy: { timestamp: "desc" },
        ...(oldCursor ? { cursor: { id: oldCursor }, skip: 1 } : {}),
      });

If I remove the orderBy

const subscribers = await prisma.subscriber.findMany({
        where: whereClause,
        take: limit,
        ...(oldCursor ? { cursor: { id: oldCursor }, skip: 1 } : {}),
      });

Works perfectly, can someone help me I need to order the response how can I fix this? Already added the indexes on prisma but still taking like 1 min to answer...

I would appreciate if someone can point me on the right direction!.


Solution

  • Ok I fix it: Just need it to add to the orderBy the id also.

    const subscribers = await prisma.subscriber.findMany({
            where: whereClause,
            take: limit,
            orderBy: [
              { timestamp: "desc" },
              { id: "desc" }, // Secondary sort for deterministic order
            ],
            ...(oldCursor
              ? {
                  cursor: {
                    id: oldCursor,
                  },
                  skip: 1,
                }
              : {}),
          });
    

    And added this indexes:

    model Subscriber {
      id         String        @id @default(uuid())
      timestamp  DateTime      @default(now())
      popup_id   String
      user_id    String
      session_id String?
      popup_type PopupTypeEnum
      user_agent String?
      referrer   String?
      url        String
      email      String
      code       String?
    
      @@index([timestamp(sort: Desc), id(sort: Desc)])
      @@index([user_id, timestamp(sort: Desc)])
    }
    

    Maybe it helps to someone.