Hi everyone. I’m stuck with a slow query I’d like to speed up. TLDR; this query is super slow. What should I do to speed this query up? Can I do it by adding an index?

prisma.concept.findMany({
    where: {
      dictionaryId: Number(dictId),
      name: {contains: foo}
    },
    include: {
      _count: {
        select: {
          declarations: true, // including this in the query makes it super slow
        },
      },
    }
  })

I have a concept table and a declaration table. Concept and declaration have a many to many relationship. The declaration table has more than 500k rows, same goes for concept table. longer explanation: https://www.loom.com/share/ff8d3034898e4ad08b8d19f044d500fe

@Yann Buydens can you drop your db schema in here?

@Jeremy Hinegardner

model Concept {
  id                                     Int                                @id @default(autoincrement())
  createdAt                              DateTime                           @default(now())
  updatedAt                              DateTime                           @updatedAt
  name                                   String                             @db.VarChar(255)
  definition                             String?
  declarations                           Declaration[]                      @relation(references: [id])
  tags                                   Tag[]                              @relation(references: [id])
  dictionary                             Dictionary                         @relation(fields: [dictionaryId], references: [id], onDelete: Cascade)
  dictionaryId                           Int
  ignore                                 Boolean?                           @default(false)
  conceptValidation                      ConceptValidation?
  DeltaAnalysisResult                    DeltaAnalysisResult?               @relation(fields: [deltaAnalysisResultId], references: [id])
  deltaAnalysisResultId                  String?
  deltaAnalysisResultsWhereConsideredNew NewConceptsInDeltaAnalysisResult[]

  @@unique([name, dictionaryId])
}

model Declaration {
  id          String    @id @default(cuid())
  name        String    @db.VarChar(255)
  concepts    Concept[] @relation(references: [id])
  location    Location  @relation(fields: [locationId], references: [id])
  locationId  String
  repo_url    String
  branch_name String
  commit_hash String
  tags        Tag[]     @relation(references: [id])
}

thx for helping me out 🙏 appreciate it

@Yann Buydens - I think the raw database schema would be more helpful - it looks like you are using an implicit many-to-many relationship between

concept

and

declaration

so there is a join table under the covers, and I'd want to look and see if there are indexes on it.

ok. @Jeremy Hinegardner is this what you need?

-- DDL generated by Postico 1.5.20
-- Not all database features are supported. Do not use for backup.

-- Table Definition ----------------------------------------------

CREATE TABLE "_ConceptToDeclaration" (
    "A" integer NOT NULL REFERENCES "Concept"(id) ON DELETE CASCADE ON UPDATE CASCADE,
    "B" text NOT NULL REFERENCES "Declaration"(id) ON DELETE CASCADE ON UPDATE CASCADE
);

-- Indices -------------------------------------------------------

CREATE UNIQUE INDEX "_ConceptToDeclaration_AB_unique" ON "_ConceptToDeclaration"("A" int4_ops,"B" text_ops);
CREATE INDEX "_ConceptToDeclaration_B_index" ON "_ConceptToDeclaration"("B" text_ops);

@Yann Buydens The join table in the middle that has concept_id and dictionary_id would be good to kno, but the concept table now has a dictionary_id in it, but it would probably be better if the dictionaryId as the first element of the index instead of the 2nd. That index won't get used unless there is also a name in the query. the join tqable between concept and diationary should have an index on both the dicationaryId and conceptId columns outsidce of that I'd ahve to see the live query that you have to do anything else. I'm heading on vacation for a couple of weeks, so unfrotunately I'll be very slow to respond. I hope this has helped a little bit, and totally understand if it hasn't

@Jeremy Hinegardner have fun on vacation! and thanks for the help. I’ll try to figure it out