Mongodb Nested Queries Not Using Indexes #18025
Description
Bug description
In my app, querying orders and their subtitle works takes around 22 secs (33908 orders, 25534 work). I reproduced my issue with a small script and schema file.
Also index usage count is not increasing in second query.
I'm currently using a local single node mongoldb replica set for reproducing, also tried in atlas replica set instance.
How to reproduce
import { PrismaClient } from '@prisma/client';
const prismaClient = new PrismaClient({
datasources: {
db: { url: 'mongourl' },
},
log: ['query', 'info', 'warn', 'error'],
});
console.time('work findmany');
await prismaClient.subtitleWork.findFirst({
where: {
orderId: '60b3e0be7a8dc3424dfd1afc',
},
});
console.timeEnd('work findmany');
console.time('order findunique');
const order = await prismaClient.order.findUniqueOrThrow({
where: {
id: '5edf8699f51916565a5eac8a',
},
include: {
subtitleWork: true,
},
});
console.timeEnd('order findunique');
Expected behavior
Including subtitleWork must use index of orderId.
Prisma information
datasource db {
provider = "mongodb"
url = env("DATABASE_URL")
}
model SubtitleWork {
id String @id @default(auto()) @map("_id") @db.ObjectId
createdAt DateTime @default(now())
updatedAt DateTime @default(now()) @updatedAt
...
order Order @relation(fields: [orderId], references: [id])
orderId String @unique @db.ObjectId
}
model Order {
id String @id @default(auto()) @map("_id") @db.ObjectId
createdAt DateTime @default(now())
updatedAt DateTime @default(now()) @updatedAt
createdBy String @db.ObjectId
updatedBy String @db.ObjectId
...
subtitleWork SubtitleWork?
}
Environment & setup
- OS: macOS
- Database: MongoDB
- Node.js version: v18.12.1
Prisma Version
4.10.1