MongoDB + Prisma query optimization (manual indexing)

make your api response 7000 ms to 80 ms with simple index creation.

this story is about handling amount of data in mongodb. before we start, let me setup my scenario. i have 200,000 records of news approx. (1.2G for news collection)

my prisma’s simple query.

const getRecentNewses = async (req, res) => {
    const from = moment(Date.now()).subtract(1, 'm').format();
    const result = await prisma.newses({
        where: {
            time_gt: from,
        }, first: 20, orderBy: "time_DESC"
    });
    res.json(result)
}

surprisingly, this took 4000 ~ 8000 ms to process, becase prisma does not automatically create index for time field, unless it is marked as @unique

api request before mongodb index

after few struggling, i found prisma does not create any index for time specified queries, i had to create manually. so i opened up MongoDB Compass, connected to mongodb, selected my database & collection, and here we are on this screen with INDEXES tab

as you can see there is no time related index. in my case there is additional originUrl’s index which prisma has created automatically. (it was marked as @unique)

now, we simply hit create index button. enter your index’s name & target field. with no additional options.

hit create index. after few seconds you will see new index is created.

go back to your api.

api request after mongodb index

voila. 7000 ms to 80 ms

I hope this was helpful to your own occasion

learn more about mongo db indexing

Indexes - MongoDB Manual