How to hande joins in Mongodb?

Question

I have two tables in PostgreSQL: urls (table with indexed pages, host is indexed column, 30 mln rows) hosts (table with information about hosts, host is indexed column, 1mln rows)

One of the most frequent SELECT in my application is:

SELECT urls.* 
FROM urls 
JOIN hosts ON urls.host = hosts.host 
WHERE urls.projects_id = ? 
  AND hosts.is_spam IS NULL 
ORDER by urls.id DESC, LIMIT ?

In projects which have more than 100 000 rows in urls table the query executes very slow.

Since the tables has grown the query is execution slower and slower. I've read a lot about NoSQL databases (like MongoDB) which are designed to handle so big tables and i'am taking into consideration move my data to MongoDB. Everything would be easy, if i didn't have to check hosts table during selecting data from urls table. I've heard that MongoDB doesn't support joins, so my question is how to solve above problem? I could put information about host in urls collection, but the field hosts.is_spam could be updated by user and i would have to update the whole urls collection. I don't know it it is right solution.

I would be greatful for any advices.

Answer 1

If you don't use joins, then relational dbs can also work pretty fast. I think, this is the case where you need to denormalize for the sake of performance.

Option 1

Copy is_spam column to the urls table. When this value of the host changes, update all related urls. This is okay if you don't do it too often.

Option 2

I don't know your app, but I assume that the number of spam hosts is relatively small. In this case, you could put their ids to an in-memory store (memcached, redis, ...), query all the urls and filter out spam urls in the app. This way your pagination gets a little broken, but sometimes it's a viable option.

Answer 2

You are correct in that the problem is the join, but my guess is that it's just the wrong kind of join. As Frank H. mentioned, PostgreSQL should be able to process this type of query rather handily depending on the frequency of hosts.is_spam. You probably want to cluster the urls table on id to optimize the order by-limit phase. Since you only care about urls.* you can minimize disk io by creating a partial index on hosts.host where is_spam is not null to make it easy to get just the short list of hosts to avoid.

Try this:

select urls.* 
from urls 
left join hosts 
   on urls.host = hosts.host 
   and hosts.is_spam is not null
where urls.projects_id = ? 
and hosts.host is null

Or this:

select * 
from urls
where urls.projects_id = ? 
and not exists (
   select 1
   from hosts
   where hosts.host = urls.hosts
   and hosts.is_spam is not null
)

This will allow PostgreSQL to use an anti-join to pull only urls which are not mapped to a known spammy host. The results may be different from your query if there are urls with a null or invalid host.

Answer 3

It is true that MongoDB doesn't support joins. In a case like this, I would structure my urls collection like this

urls : {
    name,
    some_other_property,
    host
}

You can then fetch the host for a particular URL, and check the is_spam field for it in the hosts collection. Note that this will need to be done by the client querying the DB and cannot be done at the DB itself like you would with a JOIN.

Answer 4

Similar to the answer by @xbones, but with specific examples

Putting a host_id field in your urls documents is one way to go. It will require that you first pull a result of url documents, and then pull a result of spam hosts, then filter locally in your client code

Roughly:

var urls = db.urls.find({projects_id:'ID'}, {_id: 1, host_id: 1});
var hosts = db.hosts.find({is_spam: 1}, {_id: 1});

# psuedocode
ids_array = _id for _id in urls if host_id is not in hosts

urls = db.urls.find({_id: {$in: ids_array}});

Or:

var urls = db.urls.find({projects_id:'ID'});
var hosts = db.hosts.find({is_spam: 1}, {_id: 1});

# psuedocode
urls = url for url in urls if host_id is not in hosts

The first example assumes the result of the project_id query could be huge (and your url documents are bigger) and you only wanted to grab the smallest amount of data possible, then you filter locally, and then batch get the full final url documents.

The second example just gets the full url documents to start, and filters them down locally.