0

i have this query to MongoDB

const getTopUserPosition = async (id, skip = 0, name) => { 
    /* 
    Here I get 1,000 IDis of users in descending balance
   */
    const users = await (await User.find({ admin: 0 }, { id: true, _id: false, }).skip(skip).limit(1_000).sort({
        [`${name}`]: -1
    })).map(x => x.id) /* Array of objects [ { id: 222856843 } ] to  [ 222856843 ] */

    if (!users.length) return 0 /* If havent array length i returning 0 */
    /* If i dont found user id i call this function  again. */
    if (!users.includes(id) || users.indexOf(id) < 0) return getTopUserPosition(id, skip + 1_000, name) 
    
    const userPosition = users.indexOf(id) + 1

    return userPosition + skip
},

How can i optimize this query? I'm now getting a response from Mongodb within 10 seconds

DeFaNJI
  • 1
  • 1
  • Do you have indices on any of the queried fields? Can you attach explain() of this query – Gibbs May 02 '22 at 12:35
  • @Gibbs, yes i have indexes for every fields in this query. const defaultValue = (type, value, index = false) => ({ type, default: value, index }); To create a field in a model. balancesInfo: { main: defaultValue(Number, 0), invest: defaultValue(Number, 0, true), allInvested: defaultValue(Number, 0, true), withdrawned: defaultValue(Number, 0, true), rub: defaultValue(Number, 0) } I specified an index for "all invested". p.s: Sry i cant format this code, i dont know why – DeFaNJI May 02 '22 at 12:46
  • Skipping can be very costly in MongoDB according to this [SO answer](https://stackoverflow.com/questions/7228169/slow-pagination-over-tons-of-records-in-mongodb). Consider adding a sort key /partition key and indexing it for traversing in a more performant way. – ray May 02 '22 at 12:50

1 Answers1

0

An alternative way to get the user position in your collection (if all fields have an index) is to count how many documents there are before your document.

const user = await User.find({id: id});
const userPosition = await User.find({"$gt": user[name]}).sort({name: -1}).count();
MoiioM
  • 1,914
  • 1
  • 10
  • 16