MongoDB Is Abusing JSON

Re:MongoDB Is Abusing JSON by cheald on Thursday January 17, 2013 @07:57PM (#5076376)

No, 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.

[ Parent | on HN ]

Re:MongoDB Is Abusing JSON by SigmundA on Thursday January 17, 2013 @08:46PM (#5076580)

  SELECT * 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;

[ Parent | on HN ]

Re:MongoDB Is Abusing JSON by cheald on Thursday January 17, 2013 @08:55PM (#5076601)

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

[ Parent | on HN ]

Re:MongoDB Is Abusing JSON by SigmundA on Thursday January 17, 2013 @09:05PM (#5076640)

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

[ Parent | on HN ]

Re:MongoDB Is Abusing JSON by trowftd on Friday January 18, 2013 @02:18AM (#5077547)

I'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;

[ Parent | on HN ]

Re:MongoDB Is Abusing JSON by cheald on Friday January 18, 2013 @02:49AM (#5077620)

The query is for posts tagged with all three tags, not just any one of them.

[ Parent | on HN ]