Clarification about indexes with prisma relaiton mode

[smashboy]

Hello everyone!

I'm trying to switch my prisma schema to mysql with relationMode = "prisma" and I'm a little bit confused on how should I setup my indexes. I have two issues:

A) Does the order of the fields in the index attribute matter?

B) Why vscode does not complain about second relation?

I have two relations to the same model, but vscode doesn't show a warning for the first relation if it doesn't have an index.

And if I need to set up an additional index, how should I do it?

 @@index([followerUserId, followingUserId])

or

@@index([followerUserId])
@@index([followingUserId])

[nurul3101]

Hello @smashboy 👋

Does the order of the fields in the index attribute matter

The order in which you pass the column names in the @relation attribute would be the same for creating the underlying composite index. The order of columns determines which types of queries can use the index efficiently.

Ideally, the first column in the index should have high selectivity i.e. if the first column has a large number of unique values relative to the total number of rows in the table, then it will help narrow down the search more efficiently, leading to better query performance.

Why vscode does not complain about second relation?

Do you get the warning for the second relation? Also, if you can share the schema in text format instead of message it would be much easier in trying it locally.

And if I need to set up an additional index, how should I do it?

This would depend on how you are querying. If your find query is filtering on followerUserId or followingUserId individually, then you should declare it like this
Example

  await prisma.userfollower.findMany({
    where: {
      followerUserId: '__id__',
    },
  });

@@index([followerUserId])
@@index([followingUserId])

But if your find query has filtering on both columns in the same query
Example

  await prisma.userfollower.findMany({
    where: {
      followerUserId: '__id__',
      followeingUserId: '__id__',
    },
  });

then you should define it as

 @@index([followerUserId, followingUserId])

[smashboy]

Do you get the warning for the second relation? Also, if you can share the schema in text format instead of message it would be much easier in trying it locally.

No, I get a warning only for one relation. Here is a schema:

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider          = "mysql"
  url               = env("DATABASE_URL")
  shadowDatabaseUrl = env("SHADOW_DATABASE_URL")
  relationMode      = "prisma"
}

model User {
  id        String         @id @default(cuid())
  createdAt DateTime       @default(value: now())
  updatedAt DateTime       @updatedAt
  name      String?
  followers UserFollower[] @relation("following")
  following UserFollower[] @relation("followers")
}

model UserFollower {
  createdAt       DateTime @default(value: now())
  updatedAt       DateTime @updatedAt
  following       User     @relation("following", fields: [followingUserId], references: [id])
  followingUserId String
  follower        User     @relation("followers", fields: [followerUserId], references: [id]) // only this relation has a warning
  followerUserId  String

  @@unique([followingUserId, followerUserId])
}

[nurul3101]

I was able to reproduce this as well, only the follower relation had a warning and the following relation didn't have any warning. It could be a bug in our VS Code Extension. Do you mind opening a bug report in this repository: https://github.com/prisma/language-tools/issues so that our engineering team can have a look?

[smashboy]

Yep, here it is prisma/language-tools#1400. Thank you for your help! 👍

[nurul3101]

Thank you so much for taking the time to create a bug report 🙌