4 ms·

not 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

• cheald 14y ago
  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.
  • SigmundA 14y ago
    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;
    • cheald 14y ago
      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.
      • SigmundA 14y ago
        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.
  • trowftd 14y ago
    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;
    • cheald 14y ago
      The query is for posts tagged with all three tags, not just any one of them.