MongoDB search and pagination Aggregation Performance issue

Question

I'm new in node js and MongoDB. I'm working on MongoDB search and pagination which is working good, but I have an issue with performance. it is taking too much time in counting and search records.

if I use small word to search then it works faster, if I use "long string" or "no record in database" then it takes too much time which is 50 to 186.30 seconds. (it is too much time, I'm expecting it to be 1 to 2 seconds).

I have more than 15,00,000 data on my record.

If I do not include count of the search word. it is takes 0.20 to 1.5 seconds, but when I count records while searching word it takes 25.0 to 35.0 seconds.

I have no idea how to decrease this time for counting records with the search word(query optimization).

I tried max level of query optimization.

I have also tried with

{
  $count: "passing_scores"
}

but no change on time. I'm stuck on it. I have to decrease the time of count with the search word.

SQL Query for example

  SELECT * FROM `post`
    Left JOIN catagory ON post.catid=catagory.id
    WHERE post_name LIKE '%a%' OR post_data LIKE '%a%' OR tags LIKE '%a%' OR post_url LIKE '%a%'

NODE and MongoDB

PostObj.count({},function(err,totalCount) {
        if(err) {
            response = {"error" : true,"message" : "Error fetching data"}
        }
        PostObj.aggregate([
        { $lookup:
                {
                    from: 'catagories',
                    localField: 'catagory.catagory_id',
                    foreignField: '_id',
                    as: 'catagories_data'
                }
        },
        {

            $match:
                {
                    $or: [
                        {"catagories_data.catagory_name": { $regex: new RegExp(search_data)}},
                        {"postname": { $regex: new RegExp(search_data) }},
                        {"posturl": { $regex: new RegExp(search_data) }},
                        {"postdata": { $regex: new RegExp(search_data) }},
                        {"tags": { $regex: new RegExp(search_data) }}
                    ]
                }
        },            
        { $limit : search_limit },
        { $skip : search_skip },
        { $group : { _id : "$_id", postname: { $push: "$postname" } , posturl: { $push: "$posturl" }  } } 
    ]).exec(function (err, data){  

        //end insert log data        
        if(err) {
            response = {"error" : true,"message" :err};
        } 

        if(search_data != "")
        {
            // count record using search word

            PostObj.aggregate([
                    { $lookup:
                        {
                            from: 'catagories',
                            localField: 'catagory.catagory_id',
                            foreignField: '_id',
                            as: 'catagories_data'
                        }
                },
                {

                    $match:
                        {
                            $or: [
                                {"catagories_data.catagory_name": { $regex: new RegExp(search_data)}},
                                {"postname": { $regex: new RegExp(search_data) }},
                                {"posturl": { $regex: new RegExp(search_data) }},
                                {"postdata": { $regex: new RegExp(search_data) }},
                                {"tags": { $regex: new RegExp(search_data) }}
                            ]
                        }
                },    
                { $group: { _id: null, myCount: { $sum: 1 } } },
                { $project: { _id: 0 } }   
            ]).exec(function (err, Countdata){  
                res.json({
                sEcho : req.body.draw,
                iTotalRecords: Countdata.myCount,
                iTotalDispla,yRecords: Countdata.myCount,
                aaData: data
            });
        }

        res.json({
            sEcho : req.body.draw,
            iTotalRecords: totalPages,
            iTotalDisplayRecords: totalPages,
            aaData: data
        });
    });
});

Also, I have to try this way but it is tack 35.0 to 49.0 seconds more than 1st code.

PostObj.aggregate([
    { $lookup:
               {
                            from: 'catagories',
                            localField: 'catagory.catagory_id',
                            foreignField: '_id',
                            as: 'catagories_data'
                        }
                },
                {

                    $match:
                        {
                            $or: [
                                {"catagories_data.catagory_name": { $regex: new RegExp(search_data)}},
                                {"postname": { $regex: new RegExp(search_data) }},
                                {"posturl": { $regex: new RegExp(search_data) }},
                                {"postdata": { $regex: new RegExp(search_data) }},
                                {"tags": { $regex: new RegExp(search_data) }}
                            ]
                        }
                }, 
    { '$facet'    : {
        metadata: [ { $count: "total" }, { $addFields: { page: NumberInt(3) } } ],
        data: [ { $skip: 20 }, { $limit: 10 } ] // add projection here wish you re-shape the docs
    } }
] )

If I do not use search word it is work good. I have an issue with when searching any word(count of records of that work without skip and limit)

collection data

Post

 {
   "_id": ObjectId("5d29bd7609f28633f38ccc13"),
   "postname": "this is some data ",
   "tags " : "
   Damita,
   Caro,
   Leontyne,
   Theodosia,
   Vyky ",
   "postdata ": "Berry Samara Kellia Rebekah Linette Hyacinthie Joelly Micky Tomasina Christian Fae Doralynn Chelsea Aurie Gwendolyn Tate
   Cairistiona Ardys Aubrie Damita Olga Kelli Leone Marthena Kelcy
   Cherlyn Molli Pris Ginelle Sula Johannah Hedwig Adelle Editha Lindsey
   Loleta Lenette Ann Heidie Drona Charlena Emilia Manya Ketti Dorthea
   Jeni Lorene Eolanda Karoly Loretta Marylou Tommie Leontyne Winny Cyb
   Violet Pavia Karen Idelle Betty Doloritas Judye Aretha Quinta Billie
   Vallie Fiona Letty Gates Shandra Rosemary Dorice Doro Coral Tove Crin
   Bobbe Kristan Tierney Gianina Val Daniela Kellyann Marybeth Konstance
   Nixie Andeee Jolene Patrizia Carla Arabella Berna Roseline Lira Cristy
   Hedi Clem Nerissa ",
   "catagory " : [
     { "catagory_id " : [ ObjectId("5d29bd7509f28633f38ccbfd")]},
     { "catagory_id": [ ObjectId("5d29bd7509f28633f38ccbfd") ]}],
   "createby": "5d22f712fe481b2a9afda4aa"
 }

catagory

{
  "_id": ObjectId("5d29bc271a68fb333531f6a1"),
  "catagory_name": "Katharine",
  "catagory_description": "Katharine"
}

Any solution for it?

It will be better if you can share your collection's prototype or declartions as well. — Shivam Mishra, Jul 19 '19 at 08:10
Can you please share indexes you have on your collections? Also, share regular expression (any one) you are using? — Rajat Goel, Jul 22 '19 at 05:37
The problem is in your data model. Basically, you are doing 15M `$lookups`. — Markus W Mahlberg, Jul 25 '19 at 06:02
@MarkusWMahlberg 15M lookups (index used) + (15 *4) million regex matches (without any index usage) — Rajat Goel, Jul 25 '19 at 10:06

Rajat Goel · Answer 1 · 2019-07-25T10:37:25.857

If in your case, your regex is just looking for a (or few) word(s), then it would be better to use $text instead of $regex. $text can use text index and is thus much faster. In terms of MySQL, $text is LIKE and $regex is REGEXP. Since in your example mysql query you are using LIKE, I'm pretty confident you can go for $text instead of $regex, in your mongo query as well.

You need to have (if not already) a compound "text" index on your fields - (postname, tags, postdata and posturl).

db.POST.createIndex(
   {
     postname: "text",
     tags: "text",
     posturl: "text",
     postdata: "text"
   }
 )

Shivam Mishra · Answer 2 · 2019-07-22T07:09:40.810

There are some tips that i can suggest you try.

1: POST collection

it seems you are storing only category_id inside your category array of objects property, which you should avoid. instead what you should do is as below.

create new property post_id inside category collection instead of array of object of category in post collection in [ high performance approach ].

OR

convert category property of post collection form array of object to simple array. [ average performance ]. Ex: category: [ ObjectId("5d29bd7509f28633f38ccbfd", ObjectId("5d29bd7509f28633f38ccbfd", ObjectId("5d29bd7509f28633f38ccbfd"];

definitely in both the cases post_id or category property must be indexed.

2: lookup

instead using simple lookup pipeline you should use pipeline approach

Eg:

NOT GOOD.

$lookup:{
    from: 'catagories',
    localField: 'catagory.catagory_id', // BAD IDEA //
    foreignField: '_id',
    as: 'catagories_data'
},

GOOD.

$lookup:{
    from: 'catagories',
    localField: '_id',
    foreignField: 'post_id',  // GOOD IDEA
    as: 'catagories_data'
},

EVEN BETTER


$lookup:{
    let : { post_id: "$_id" },
    from: 'catagories',
    pipeline:[
              {
                    $match: {
                        $expr: {
                            $and: [
                                { $eq: ["$post_id", "$$post_id"], },
                            ]
                        }
                    },
                },
                {
                    $match: {
                        $or: [

                            // AVOID `new` keyword if you can do such;
                            // and create indexes for the same;

                            { "catagory_name": { $regex: `^${search_data}` } },
                            { "postname": { $regex: `^${search_data}` } },
                            { "posturl": { $regex: `^${search_data}` } },
                            { "postdata": { $regex: `^${search_data}` } },
                            { "tags": { $regex: `^${search_data}` } }
                        ]
                    }

                }
    ],
    as: 'catagories_data'
},

After All facet pipeline seems fine to me.

'$facet' : {
    metadata: [ { $count: "total" }, { $addFields: { page: NumberInt(3) } } ],
    data: [ { $skip: 20 }, { $limit: 10 } ] // add projection here wish you re-shape the docs
}

Other aspects of slowdown query depends on

configuration of your backend server and database server.
distance between frontend -> backend -> database server.
incoming and outgoing request per second.
internet connection of course

Complete Query will look like this

PostObj.aggregate([
    {
        $lookup: {
            let: { post_id: "$_id" },
            from: 'categories',
            pipeline: [
                {
                    $match: {
                        $expr: {
                            $and: [
                                { $eq: ["$post_id", "$$post_id"], },
                            ]
                        }
                    },
                },
                {
                    $match: {
                        $or: [

                            // AVOID `new` keyword if you can do such;
                            // and create indexes for the same;

                            { "catagory_name": { $regex: `^${search_data}` } },
                            { "postname": { $regex: `^${search_data}` } },
                            { "posturl": { $regex: `^${search_data}` } },
                            { "postdata": { $regex: `^${search_data}` } },
                            { "tags": { $regex: `^${search_data}` } }
                        ]
                    }

                }
            ],
            as: "catagories_data"
        }
    },
    {
        '$facet': {
            metadata: [{ $count: "total" }, { $addFields: { page: NumberInt(3) } }],
            catagories_data: [{ $skip: 0 }, { $limit: 10 }]
        }
    }
])

it is give error from: 'catagories', SyntaxError: Unexpected identifier — JON, Jul 22 '19 at 06:15
quamma was missing at `let : { post_id: "$_id" }`. i just fixed it. and it is just an reference for flow. — Shivam Mishra, Jul 22 '19 at 06:26
it seems you are copying query. you should concentrate on flow and build query based on that. but it's ok let me create and execute the scenario in my local environment. — Shivam Mishra, Jul 22 '19 at 06:40
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/196807/discussion-between-shivam-mishra-and-jon). — Shivam Mishra, Jul 22 '19 at 11:53

MongoDB search and pagination Aggregation Performance issue

2 Answers2