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!.

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!.

• javascript
• prisma

2 Answers 2

You probably want to run a db profiler or queryplanner to diagnose properly, but at a first glance the issue may be that your index has fields - bothuserid and timestamp. Have you tried adding just a timestamp index, like @@index(timestamp(sort: Desc)) ?

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.