File: src/app/api/doubts/route.ts
const replyCountSql = sql`coalesce((SELECT count(*) FROM replies WHERE replies.doubt_id = doubts.id), 0)`;
This is a correlated subquery that runs once per row. For 20 results, this is 20 additional queries.
Fix: Use a LEFT JOIN with GROUP BY, or batch-fetch reply counts for the returned page of doubt IDs:
const replyCountsMap = await db.select({
doubtId: repliesTable.doubtId,
count: count(),
}).from(repliesTable)
.where(inArray(repliesTable.doubtId, doubtIds))
.groupBy(repliesTable.doubtId);
File:
src/app/api/doubts/route.tsThis is a correlated subquery that runs once per row. For 20 results, this is 20 additional queries.
Fix: Use a LEFT JOIN with GROUP BY, or batch-fetch reply counts for the returned page of doubt IDs: