P
Prisma5mo ago
fotoflo

Fixing slow query

Hey guys, any idea why this query might suddenly take 9 seconds?
export async function getStatusChecks(urlId: string) {
console.time("getStatusChecks");

const millisInDay = 1000 * 60 * 60 * 24;
const result = await prisma.statusChecks.findMany({
where: {
urlId: { equals: urlId },
createdAt: { gte: new Date(Date.now() - 4 * millisInDay) },
},
orderBy: {
createdAt: "asc",
},
take: 50,
});

console.timeEnd("getStatusChecks");
return result;
}
export async function getStatusChecks(urlId: string) {
console.time("getStatusChecks");

const millisInDay = 1000 * 60 * 60 * 24;
const result = await prisma.statusChecks.findMany({
where: {
urlId: { equals: urlId },
createdAt: { gte: new Date(Date.now() - 4 * millisInDay) },
},
orderBy: {
createdAt: "asc",
},
take: 50,
});

console.timeEnd("getStatusChecks");
return result;
}
schema:
model statusChecks {
id String @id @default(cuid())
createdAt DateTime @default(now()) @db.Timestamp(0)
bodySize Int?
effectiveUrl String @db.VarChar(4096)
errorCode String?
httpCode Int?
httpCodeLang String?
redirectCount Int?
totalTime Decimal
status Boolean
urlId String @db.VarChar(25)
urls urls @relation(fields: [urlId], references: [id], onDelete: Cascade)
}
model statusChecks {
id String @id @default(cuid())
createdAt DateTime @default(now()) @db.Timestamp(0)
bodySize Int?
effectiveUrl String @db.VarChar(4096)
errorCode String?
httpCode Int?
httpCodeLang String?
redirectCount Int?
totalTime Decimal
status Boolean
urlId String @db.VarChar(25)
urls urls @relation(fields: [urlId], references: [id], onDelete: Cascade)
}
Maybe i need to add some indices?
7 Replies
fotoflo
fotoflo5mo ago
adding some indexes... solved @@index([urlId, createdAt])
Nurul
Nurul5mo ago
Thanks for letting us know that adding indexes resolved the issue! 😄 🙌
fotoflo
fotoflo5mo ago
@Nurul yeah im learning that indexing is KEY! (pun intended haha)
Nurul
Nurul5mo ago
Haha, I can totally relate to that 😄
fotoflo
fotoflo5mo ago
If you work at prisma i would suggest adding more knowledge about indexing... it seems a lot of queries do a full table scan if there's not an index @Nurul... and you don't know this until you start to have 10k rows in a table - aka early production
Nurul
Nurul5mo ago
Yes, I do work at Prisma 🙂 Thank you for the feedback, I'll definetely share it internally. Did you had a chance to look at this series we did a while ago? https://www.prisma.io/blog/improving-query-performance-using-indexes-1-zuLNZwBkuL
Prisma
Improving query performance with database indexes using Prisma: Int...
Learn the fundamentals of database indexes: what they are, the problem they solve, and their benefit and cost
fotoflo
fotoflo5mo ago
no i didnt see it thanks reading now