CODEWITHSUNDEEP
mongodbindexingmongoose
manihiki
manihiki

Reputation: 682

What's the optimal way to create complex Mongoose/Mongo queries involving multiple filters, sorting and limiting?

I'm trying to construct a complex query to fetch data using multiple filter criteria. While my code was working fine for small tests, as I'm scaling to more users and data queries are becoming unacceptably slow (eg 20-30 seconds to complete a request). I'm not sure if there's a way I should be using indexes or if they don't apply in complex queries like this, or if I should use multiple separate queries, or I need to totally restructure the queries or the data. The code below is an example query, say finding users whose user ID is in a given array but not in others, and who's preferences match certain filter criteria, and who are in a given geography. I'm pretty sure I'm breaking some cardinal rules here, but am a relative newb so any help is greatly appreciated! In particular, if an index is what's needed, it'd help to have an example of how to use one in this example case. Thank you!

User.find({
  $and: [
    { userid: {$in: array1} },
    { userid: {$nin: array2} },
    { userid: {$nin: array3}},
    { userPref1: {$ne: userPref} },
    { userPref2: {$ne: userPref2} },
    { latlon : {"$within" :           
            {"$center" : [userlocation , distance ]}
          } }
          ]},
    .sort('-hasAccount -hasActions')
    .limit(max)
    .exec(function(...){});

Upvotes: 0

Views: 639

Answers (1)

Mark_H
Mark_H

Reputation: 790

  1. "finding users whose user ID is in a given array but not in others"

    Merge "{ userid: {$in: array1} },{ userid: {$nin: array2} },{ userid: {$nin: array3}}" into one. Get userid who are in array1 but not array2&array3 into array4, then use { userid: {$in: array4} }

  2. "who's preferences match certain filter criteria"

    If there are only 2 userPref, build compound index will be ok. But if there are dozens... no way. You have to leave unimportant userPref without index, which will definitly make the query slow.

  3. "who are in a given geography"

    2d index is necessary.

Summary:

if（num of userPrefs < M) // I would set M = 3  
    build index: {loc:"2d", userid:1, userPref1:1, userPref2:1} or
    build index: {loc:"2d", userid:1, userPref1:1};
    sort({loc:"2d", userid:1, userPref1:1});
else
    reduce num of userPrefs;

BTW: sort by other order instead of {loc:"2d", userid:1, userPref1:1} will also slow the query.

Upvotes: 3

Related Questions