MongoDB search and pagination Aggregation Performance issue

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"   } )

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 }]        }    }])