3 ms·
Aggregation is absolutely one of Mongo's weaknesses. It's not great at ad-hoc aggregation like MySQL or whatnot is, and the fact that it tends to lend itself to
by cheald 14y ago
Aggregation is absolutely one of Mongo's weaknesses. It's not great at ad-hoc aggregation like MySQL or whatnot is, and the fact that it tends to lend itself to denormalized data makes SQL-style reporting clunky at best.
It does a lot of things better than SQL, too. Consider, for example, the query "Give me a list of all posts with all of these tags, by any of these authors, sorted by post date descending"
db.posts.find({
tags: {$all: ["foo", "bar", "baz"]},
author: {$in: ["Joe", "Jane"]}
}).sort({post_date: -1})
In SQL, you'd end up with something like:
SELECT posts.* FROM posts
INNER JOIN post_tags t1 ON t1.tag = "foo" AND t1.post_id = posts.id
INNER JOIN post_tags t2 ON t2.tag = "bar" AND t2.post_id = posts.id
INNER JOIN post_tags t3 ON t3.tag = "baz" AND t3.post_id = posts.id
WHERE posts.author = "Joe" or post.author = "Jane"
ORDER BY post_date DESC;
Its strength is denormalization; since you can denormalize entire lists or maps of data into a document, and then index and query on them, you can end up performing queries that would be ridiculously ugly and tedious in SQL.
- djb_hackernews 14y agonot to take away from any of your great points in this post but isn't it the same as: SELECT posts.* FROM posts INNER JOIN post_tags pt ON pt.post_id = posts.id AND pt.tag IN ('foo', 'bar', 'baz') WHERE posts.author IN ('Joe', 'Jane') ORDER BY post_date DESC; My SQL is rusty, I could be missing something but they seem essentially equivalent if you use the SQL helpers such as IN.
- cheald 14y agoNo, because that'll select any post that contains any of those three tags, not the posts that contain all three tags. AND vs OR. It's worth noting that I threw the SQL query a bone by denormalizing post_tags into one table. In a properly relational DB, you'd have a tags table, a posts table, and a post_tags join table, so the query gets even hairier (or you have to do two queries). SELECT posts.* FROM posts INNER JOIN post_tags pt1 ON pt1.post_id = posts.id INNER JOIN tags t1 ON t1.tag = "foo" and pt1.tag_id = t1.id INNER JOIN post_tags pt2 ON pt2.post_id = posts.id INNER JOIN tags t2 ON t3.tag = "bar" and pt2.tag_id = t2.id INNER JOIN post_tags pt3 ON pt3.post_id = posts.id INNER JOIN tags t3 ON t3.tag = "baz" and pt3.tag_id = t3.id WHERE posts.author = "Joe" or post.author = "Jane" ORDER BY post_date DESC; Yikes. It's also worth noting that this generates a massive temp table to be sorted, which is very likely going to end up causing you to have to do a filesort. In practice, you'd probably break this down into 3 queries (forgive my mixing languages): $tag_ids = SELECT id FROM tags WHERE tag IN ("foo", "bar", "baz") $post_ids = SELECT posts.id FROM posts INNER JOIN post_tags pt1 ON pt1.post_id = posts.id and pt1.tag_id = $tag_ids[0] INNER JOIN post_tags pt2 ON pt2.post_id = posts.id and pt2.tag_id = $tag_ids[1] INNER JOIN post_tags pt3 ON pt3.post_id = posts.id and pt3.tag_id = $tag_ids[2] WHERE posts.author = "Joe" or posts.author = "Jane" $posts = SELECT posts.* FROM posts WHERE id IN ($post_ids) ORDER BY post_date DESC; Easily doable in both languages, but Mongo's denormalized structure makes this sort of use case a ton simpler.
- SigmundA 14y agoSELECT * FROM post WHERE id IN ( select post_id from post_tags INNER JOIN tags ON post_tag.tag_id = tag.id WHERE tag.tag in ("foo","bar","baz") GROUP BY post_id HAVING COUNT(*) = 3 ) AND author IN ("Joe","Jane") ORDER BY post_date DESC; Of course normalizing tags is silly as they are natural keys so it should be as you first stated: SELECT * FROM post WHERE id IN ( select post_id from post_tags WHERE tag in ("foo","bar","baz") GROUP BY post_id HAVING COUNT(*) = 3 ) AND author IN ("Joe","Jane") ORDER BY post_date DESC;
- cheald 14y agoAre subselects still ridiculously inefficient in MySQL? It's been a long while since I've used them. I do like how those queries read, though.
- SigmundA 14y agoSorry I only use proper DB's like PostgreSQL and MSSQL who optimizer's have no problems with this ;). Also what I like about the count approach is you can do "show me all the posts with at least 2 out of the 3 tags matching" fuzzier search if you desire.
- trowftd 14y agoI'm not sure what you mean, but the parent's SQL is valid and returns the same data as the gp's, only in a more efficient manner. No need to do three inner joins when you can get away with one. In fact, you can take out the second part of the inner join condition and move it in the where clause, like this (execution plan will stay the same): SELECT posts.* FROM posts INNER JOIN post_tags pt ON pt.post_id = posts.id WHERE posts.author IN ('Joe', 'Jane') AND pt.tag IN ('foo', 'bar', 'baz') ORDER BY post_date DESC;
- cheald 14y agoThe query is for posts tagged with all three tags, not just any one of them.
- gumbo 14y agoHashes are very handy and that a strength of mongo. However as someone using mongo on a daily basis (and often from within the console), i need to admit that performing query is hard. However if you are using it from a app you are building there are many library that make it easier.