Query for finding collection entries matching an arbitrary set of URLs

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 http://codereview.stackexchange.com/ but also have an array of 5 other URLs (such as bit.ly 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?

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
       else
           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.