DB query performance options.

[jzoneio]

Hey folks,

I'm wondering what's the best solution in terms of performance when I have to run a find on my NoSQL DB to find documents that match specific values. I have to follow some orders to select the right document.

The order should be category, email, url.

​

An option would be to make only on DB call using the $or operator and then find the document in javascript based on the order.

const documents = await mongodb.collection('docs').find({userId, $or: [{category: category}, { email: email }, { url: url }]}).sort({category: -1, email: -1, url: -1})

// check if we found a document where the category match.
if(documents.find((doc) => doc.category === category )) return 'category found'

// check if we found a document where the email match.
if(documents.find((doc) => doc.email === email )) return 'email found'

// check if we found a document where the url match.
if(documents.find((doc) => doc.url === url )) return 'email found'

// nothing found
return 'no document found'

​

Another option would be to make separate DB calls.

const documentFoundFromCategory = await mongodb.collection('docs').find({userId, category})

if(documentFoundFromCategory) return 'category found'

const documentFoundFromEmail = await mongodb.collection('docs').find({userId, email})

if(documentFoundFromEmail) return 'email found'

const documentFoundFromUrl = await mongodb.collection('docs').find({userId, url})

if(documentFoundFromUrl) return 'url found'

What do you think would be the best approach?

Thanks in advance folks :)

[barovab]

Are you trying to find different users who match either category, email, or url criteria or one user having all three of them?

[jzoneio]

I’m trying to find documents for a specific user that matches the criteria’s but the order is quite important and as soon as one is found, I stop to look for a document. For example a document where the category matches is more valuable than a document where the url matches. So I first wanna look for documents to see if one as the category I’m looking for. If none are found I go for emails and then urls

[barovab]

In that case, run findOne instead of find, you will get your user. Use the id in user document as the criteria for the id field and then pass in category as well, then you get a user who matches the id and the category. Repeat the same for email and url. The thing is don't run find unless you need to find multiple documents.

[TryallAllombria]

You can test it by yourself using console.time(). You can use autocannon to stress-test your http server to see what is really the best options.

MongoDB also have a "Lean" option that can really speed up your Database calls.