Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

MongoDB Is Abusing JSON

smsohan.com

Post reply on HN
Print view View original

51–60 of 61 posts

Re: MongoDB Is Abusing JSON

#51

Post by SigmundA »

cheald wrote: post #50
SigmundA wrote: post #49

Earlier quoted context omitted.

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","b…

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.

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.

Re: MongoDB Is Abusing JSON

#52

Post by coffeemug »

istvanp wrote: post #29
mglukhovsky wrote: post #8

Here's what this query looks like in RethinkDB (also based on JSON documents): r.table('orders') .pluck('cust_id','ord_date','price') .groupBy('cust_id','ord_date', r.sum('price')). .filter(r.row('reduction').gt(250)) We use the hard-coded attribute 'reduction' because groupBy automatically gets compiled to our distributed map-reduce infrastructure. There is currently no as command (though it could easily be simulate…

What are the main differences between MongoDB and RethinkDB? Things that you hear people care about? I don't use Mongo but I've seen projects that use it. I ask because I want to store & retrieve some analytics. After seeing the screencast of RethinkDB it seems to me that it's just like Mongo but a much better API and easier handling of sharding and replication. The only con I see of using RethinkDB is that it's very…

We're going to publish some comparisons in a few days, but basically you nailed it (both pros and con). We're trying to build a stellar data-oriented development environment/query language, and a stellar scalability infrastructure. All the main pieces are there, and Rethink is a lot of fun to use. The rough edges will be polished out in over the next few months.

Re: MongoDB Is Abusing JSON

#54

Post by Firehed »

nslater wrote: post #43
Firehed wrote: post #18

Earlier quoted context omitted.

People seem to be confusing "NoSQL" and "non-relational". Mongo happens to be both, but there's nothing fundamental that puts the two together. You may not get the full capabilities of SQL with non-relational data (JOINs, etc), but there's no reason that non-relational data stores couldn't parse normal SQL and execute the appropriate queries. You can make a relational database that doesn't support the SQL syntax, and…

How is a query not data?

In the same way that code isn't data.

Yes, it's bytes in memory like any other data, but it's the means rather than the end. You care about the data, and the query (or code) is what you use to get it.

Re: MongoDB Is Abusing JSON

#55

Post by alanctgardner2 »

corresation wrote: post #45
alanctgardner2 wrote: post #38

Earlier quoted context omitted.

This is an interesting statement to me. My goal in retrieving data is normally to reduce the result set as fast as possible. If I only need a subset of the data, I would do the bare amount of joining, aggregation, etc. before paginating. I assume the alternative is paginating server-side, which wastes some network bandwidth and processing time on the server.

If I only need a subset of the data, I would do the bare amount of joining, aggregation, etc. before paginating. The vast majority of pagination procedures occurs on the final rendered set. Meaning you've done 100% of the work on the database server to retrieve n% of the product. And when you come back for page 2, you're again doing 100% of the work for n% of the product. Pagination is not a SQL shortcut, and the sol…

I think your whole argument is based on the false assumption that you have to paginate after you've done everything else. If you have a well-normalized database, any decently complex query will involve a bunch of joins. If your sort is keyed on one or two fields in one table, why not sort and filter that table and join the result to the others? Admittedly I'm a bit of a DB novice, and perhaps you can speed the join up better by indexing, but it seems like to get 50 rows from two 1000 row tables, it makes more sense to filter 50 rows, then join, rather than the other way around.

If you're keeping the result server-side, how are you storing it between requests in a stateless context like a web app? If I ask for page 1, and you get all the pages, do you cache them server-side? That would add a ton of complexity. I suspect your app is sending the first n rows to the client and throwing the rest away.

Re: MongoDB Is Abusing JSON

#56

Post by cerales »

mattparlane wrote: post #16

I've been through the hassle of programatically piecing together complex SQL queries, and I'd far rather be able to just put together hashes that represent my query. SQL was originally designed so that people who were savvy but not necessarily developers were able to query databases, but I can't think of the last time my boss would have wanted to run some random query against our production database.

Surely by the time you're programmatically generating SQL queries you should be using either an ORM or some other kind of SQL expression language embedded in a more expressive language (such as SQLAlchemy or the myriad lisp DSPs for relational databases)?

Re: MongoDB Is Abusing JSON

#57

Post by trowftd »

cheald wrote: post #32
djb_hackernews wrote: post #31

Earlier quoted context omitted.

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

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 JOI…

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;

Re: MongoDB Is Abusing JSON

#58

Post by anko »

corresation wrote: post #46
kogir wrote: post #39

Earlier quoted context omitted.

How about any cases where you don't want to transfer all the rows you're skipping over the wire? As in, nearly all cases?

You wouldn't be "skipping" those rows because they would be transferred once. The database is almost always the most constrained part of a solution, and the various hack pagination techniques are almost always (not 100%. More like 98%) a naive mistake.

I am really interested in this argument, because I am currently converting a mysql based web search to use apache solr. I have been thinking about all the arguments about pagination a lot.

The thing I don't know about, though, is the "database is almost always the most constrained part of a solution". On this simple site search, we tend to notice the apaches taking a lot more cpu than the databases calls. I guess when our site gets bigger, you'd imagine it is easier to scale out the apaches than the database (with sharding), but we would still have a lot of room to improve the mysql layer anyway (memcached for example).

Re: MongoDB Is Abusing JSON

#59

Post by anko »

deepinsand wrote: post #53

I think it's great. I've spent too much time parsing SQL strings into well typed data structures, and you get it for free with Mongo.

I kinda agree (about the free part), but I just wanted to say that if you are parsing sql strings you really should get an ORM. Plug in a well tested, optimised library and never think about parsing sql strings again. It also makes things easier to test because you get nice separation of concerns.

Re: MongoDB Is Abusing JSON

#60

Post by cheald »

trowftd wrote: post #57
cheald wrote: post #32

Earlier quoted context omitted.

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 JOI…

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 W…

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