Complex-ish mongo query runs fairly slow, combination of $and $or $in and regex

Question

I'm running some queries to a mongodb 2.4.9 server that populate a datatable on a webpage. The user needs to be able to do a substring search across multiple fields, sort the data on various columns, and flip through the results in pages. I have to check multiple fields for matches since the user could be searching for anything related to the documents. There are about 300,000 documents in the collection so the database is relatively small.

I have indexes created for the created_by, requester, desc.name, metaprogram.id, program.id, and arr.programid fields. I've also created indexes [("created", 1), ("created_by", 1), ("requester", 1)] and [("created_by", 1), ("requester", 1)] at the suggestion of Dex.

It's also worth mentioning that documents might not have all of the fields that are being searched for here. Some documents might have a metaprogram.id but not the other ID fields for example.

An example of a query I might run is

{
    "$query" : {
        "$and" : [
            {
                "created_by" : {"$ne" : "automation"},
                "requester" : {"$in" : ["Broadway", "Spec", "Falcon"] }
            },
            {
                "$or" : [
                    {"requester" : /month/i },
                    {"created_by" : /month/i },
                    {"desc.name" : /month/i },
                    {"metaprogram.id" : {"$in" : [708, 2314, 709 ] } },
                    {"program.id" : {"$in" : [708, 2314, 709 ] } },
                    {"arr.programid" : {"$in" : [708, 2314, 709 ] } }
                ]
            }
        ]
    },
    "$orderby" : {
        "created" : 1
    }
}

with differing orderby, limit, and skip values as well.

Queries on average take 500-1500ms to complete.

I've looked into how to make it faster, but haven't been able to come up with anything. Some of the text searching stuff looks handy but as far as I know each collection only supports at most one text index and it doesn't support pagination (skips). I'm sure that prefix searching instead of regex substring matches would be faster as well but I need substring matching.

Is there anything you can think of to improve the speed of a query like this?

Do not paginate with skip and limit. Use the sort on date. See http://stackoverflow.com/questions/20960815/range-query-for-mongodb-pagination. I think you need different indexes, but, to say more, it would be useful to see an explain for one of those queries. — wdberkeley, Jan 30 '15 at 15:45

score 0 · Answer 1 · edited May 23 '17 at 12:20

It's quite hard to optimize a query when it's unpredictable. Analyze how the system is being used and place indexes on the most popular fields. Use .explain() to make sure the indexes are being used.

Also limit the results returned to a value of 50 or 100. The user doesn't need to see everything at once.

Try upgrading mongodb to see if there's a performance improvement.

Side note:

You might want to consider using ElasticSearch as a search engine instead of Mongodb. ElasticSearch would store the searchable fields and return the Mongodb Ids for matched results. ElasticSearch is a magnitude faster as a search engine than Mongodb.

Complex-ish mongo query runs fairly slow, combination of $and $or $in and regex

1 Answers1

Side note:

More info: