Schema

model Profile {
  id              String?     @unique
  username        String      @id
  profile_pic_url String?
  follower        Int?
  following       Int?
  images          Image[]

  @@map("profile")
}

model Location {
  id             String           @unique
  name           String           @id
  images         Image[]

  @@map("location")
}

model Hashtag {
  id            String?         @unique
  name          String          @id
  images        Image[]
  
  @@map("hashtag")
}

model Image {
  id              String           @id
  thumbnail       String
  shortcode       String           @unique
  like_count      Int
  comment_count   Int

  profile         Profile?         @relation(fields: [profileUsername], references: [username])
  profileUsername String?
  hashtag         Hashtag?         @relation(fields: [hashtagName], references: [name])
  hashtagName     String?
  location        Location?        @relation(fields: [locationId], references: [id])
  locationId      String?

  // Is this ok?
  @@index([id, profileUsername])
  @@index([id, hashtagName])
  @@index([id, locationId])
  // 
  @@map("image")
}

Hey guys, quick question - is it ok to set indexes on my relations? I've experienced slow reads but since I added the indexes above the speed has increased a lot! Is this safe to do or will it cause problems in the future when the tables get bigger?

@Phil Bookst 👋 Only add indexes on values that you would query for frequently. Also in case of indexes, the writes would be slow with more number of indexes.

u can enable debug to check the underlying query or just set env debug="*"

hey guys thanks for the help! I include the image field in all of my queries so setting these indexes would be ok right?

Depends on your query. If you’re querying for

id

combined with other fields (using an

AND

) then the extra indexes are not required.

also is there a difference in doing this

model Image {
  id              String           @id
  thumbnail       String
  shortcode       String           @unique
  like_count      Int
  comment_count   Int

  profile         Profile?         @relation(fields: [profileUsername], references: [username])
  profileUsername String?
  hashtag         Hashtag?         @relation(fields: [hashtagName], references: [name])
  hashtagName     String?
  location        Location?        @relation(fields: [locationId], references: [id])
  locationId      String?

  @@index([id, profileUsername]) <= index here
  @@index([id, hashtagName])
  @@index([id, locationId])
   
  @@map("image")
}

and this

model Profile {
  id              String?     @unique
  username        String      @id
  profile_pic_url String?
  follower        Int?
  following       Int?
  images          Image[] 

  // index here
  @@index([username, images])
  @@map("profile")
}

findUnique

on which of the fields?

username

and

id

?

just username

In that case, only add an index to

username

.

Yes but the images field is the one slowing down the query without the index on it

Adding the index on the foreign key i.e.

profileUsername

should work then as the relation would be fetched from there.

Ok so just like in my first example right? setting it like this

@@index([id, profileUsername])

in the images table? or like this

@@index([profileUsername])

? Thank you very much for your help, I appreciate it big time!

I don’t think you need a combined index. Just

@@index([profileUsername])

should work.