Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Most of my slow queries on my server are due to a single "find" query.

I have a collection that represents website pages. I want to be able to look up a page in the collection by URL. The caveat is that a website might have URLs that redirect to it. I've modeled my schema (with Node.js/Mongoose) like this:

        'url':                      { type: String, index: {unique: true, dropDups: true} },
        'redirects':                { type: Array, index: true },
        // ... other stuff

In other words, one entry might have the URL but also have an array of 5 other URLs (such as URLs) that redirect to it.

Later, I want to query the collection to see if we have an entry which matches an arbitrary set of URLs. I don't know if the URLs are redirected URLs or what -- I just want to find all matches in my collection that represent these URLs...

So I do this:

// urls is an array of URL strings we want to find...
model.find({$or: [{'url': {$in: urls}}, {'redirects': {$in: urls}}]}).lean().exec( ... );

Unfortunately, I might be looking for around 200 URLs at once, so this query sometimes takes > 1 second (!!!).

Is there further optimization I can do, or would it be better to split the query into multiple queries, capping the search-size each time?

share|improve this question

closed as unclear what you're asking by Jamal Dec 7 '15 at 17:14

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

I haven't used MongoDB, but I have a few suggestions based on experiences with optimization and other databases.

  1. Index hashes of the URLs and search for those instead. Using a simple MD5 hash would probably speed up searching with the cost of dealing with false positives (unlikely but possible).

  2. Store every URL as a top-level object and add a redirectsTo attribute. This alleviates the need to index or search the redirects collections. While now you need to perform two queries--one for the original URLs and another for the redirected URLs--this could end up being faster if only a small percentage of URLs are redirects.

    Here's some psuedocode to clarify what I mean:

    all = []
    found = model.find({'url': {$in: urls}})...
    redirects = []
    for each item in found
        if item.redirectsTo
            redirects += item.redirectsTo
            all += item
    redirected += model.find({'url': {$in: redirects}})...
    for each item in redirected
        all += item
  3. Update your question with the higher-level problem you're trying to solve. I often find that when I'm trying to optimize slow task T to solve problem P, the better approach is to rethink P.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.