4 ms·

I'm no SQL guru, but won't that subselect will break down when users and events grow to the millions? I probably should have thought out the example a little m

by angilly 16y ago

I'm no SQL guru, but won't that subselect will break down when users and events grow to the millions? I probably should have thought out the example a little more. We don't ever actually write that kind of query against our production database @Punchbowl. We have a data warehouse pull out high level stats every night, and we query that. WRT aggregations, you're right -- they do require a bit of acclimation. Once you write a few, though, you're good to go.

ergo98 16y ago
>I'm no SQL guru, but won't that subselect will break down when users and events grow to the millions? A moderately decent database server would have no issues in such a case, however yes, optimization would be case-specific, just as it would be with MongoDB. I was simply comparing readability of SQL for the example that you gave.
angilly 16y ago
Got it. I agree. The "where exists (select 1...)" thing was neat. Learned me something.
- nerfhammer 16y ago
  I believe that select * is actually conventional for an exists subquery
  - ergo98 16y ago
    I have a natural aversion to "SELECT *" (it is almost always a bad usage), so while any decent database system will parse it to yield the same query plan, I still use SELECT 1.
lobster_johnson 16y ago
> I'm no SQL guru, but won't that subselect will break down when users and events grow to the millions? Databases like PostgreSQL are excellent at performing joins -- which is all this subselect really is, namely joining two relations -- even when the datasets are quite large. But this particular MongoDB query comparison is pretty worthless, since it's simply giving an example of denormalization, a concept which is equally applicable to relational databases -- the main difference being that with MongoDB, you hardly have a choice in the matter, since joins don't exist. Don't get me wrong, I love MongoDB, but there are much better reasons to use MongoDB, such as the fact that every document is a flexible data structure, not a strict collection of columns. You can add keys and values as you choose, and store them as arrays or sub-documents depending on the encapsulation you need, etc. So generally you will have an easier time working with data and being impulsive about it, than the square-hole-fitting-only-square-pegs model of relational databases, which require more planning and schema design, which in turn tends to squeeze all the fun out of working with databases. There are pros and cons to both approaches, of course. MongoDB is not as mature as modern relational databases, by far. On the other hand, it has a nice feature which nobody apparently mentions: With MongoDB, the old relational theorist's pet peeve about the meaning of null values becomes moot, because in MongoDB a null value (ie., a missing value) is simply a value which is not there, ie. its key is simply not there. That's much better than null values! Another advantage is the ability to work with hetereogenous collections of data without having to jump through too many hoops. For example, you can have a collection (table) called "publications". In this table you can store different kinds of publications: Books, magazines, comics, newspapers and so on. Each type of publication may have some common fields, but many have type-specific fields -- hence, hetereogenous data. A relational database designer will tell you that in the relational world, you would denormalize. A central "publications" table with all the common columns, and then tables "books", "magazines", etc., with each table having their type-specific columns, and also having a foreign-key reference back to the "publications" table. Fine. But think of all the joins you will need just in order to list all and query this stuff; if you have only the publication ID, you have to go through all the tables to determine what type of publication it is. There's not just the performance aspect. The relational model is quite different to how people _think_ about data. MongoDB is easier on the brain, that way.
- angilly 16y ago
  > Don't get me wrong, I love MongoDB, but there are much better reasons to use MongoDB, such as the fact that every document is a flexible data structure, not a strict collection of columns. You can add keys and values as you choose, and store them as arrays or sub-documents depending on the encapsulation you need, etc. Yup. I dropped the ball on this one. Should have a list of 4 reasons. :)
- aaronblohowiak 16y ago
  You are using de-normalization in a way that i am no familiar with: http://en.wikipedia.org/wiki/Database_normalization http://en.wikipedia.org/wiki/Database_normalization

by angilly 16y ago

ergo98 16y ago

>I'm no SQL guru, but won't that subselect will break down when users and events grow to the millions? A moderately decent database server would have no issues in such a case, however yes, optimization would be case-specific, just as it would be with MongoDB. I was simply comparing readability of SQL for the example that you gave.

angilly 16y ago

Got it. I agree. The "where exists (select 1...)" thing was neat. Learned me something.

nerfhammer 16y ago
I believe that select * is actually conventional for an exists subquery
- ergo98 16y ago
  I have a natural aversion to "SELECT *" (it is almost always a bad usage), so while any decent database system will parse it to yield the same query plan, I still use SELECT 1.

lobster_johnson 16y ago

> I'm no SQL guru, but won't that subselect will break down when users and events grow to the millions? Databases like PostgreSQL are excellent at performing joins -- which is all this subselect really is, namely joining two relations -- even when the datasets are quite large. But this particular MongoDB query comparison is pretty worthless, since it's simply giving an example of denormalization, a concept which is equally applicable to relational databases -- the main difference being that with MongoDB, you hardly have a choice in the matter, since joins don't exist. Don't get me wrong, I love MongoDB, but there are much better reasons to use MongoDB, such as the fact that every document is a flexible data structure, not a strict collection of columns. You can add keys and values as you choose, and store them as arrays or sub-documents depending on the encapsulation you need, etc. So generally you will have an easier time working with data and being impulsive about it, than the square-hole-fitting-only-square-pegs model of relational databases, which require more planning and schema design, which in turn tends to squeeze all the fun out of working with databases. There are pros and cons to both approaches, of course. MongoDB is not as mature as modern relational databases, by far. On the other hand, it has a nice feature which nobody apparently mentions: With MongoDB, the old relational theorist's pet peeve about the meaning of null values becomes moot, because in MongoDB a null value (ie., a missing value) is simply a value which is not there, ie. its key is simply not there. That's much better than null values! Another advantage is the ability to work with hetereogenous collections of data without having to jump through too many hoops. For example, you can have a collection (table) called "publications". In this table you can store different kinds of publications: Books, magazines, comics, newspapers and so on. Each type of publication may have some common fields, but many have type-specific fields -- hence, hetereogenous data. A relational database designer will tell you that in the relational world, you would denormalize. A central "publications" table with all the common columns, and then tables "books", "magazines", etc., with each table having their type-specific columns, and also having a foreign-key reference back to the "publications" table. Fine. But think of all the joins you will need just in order to list all and query this stuff; if you have only the publication ID, you have to go through all the tables to determine what type of publication it is. There's not just the performance aspect. The relational model is quite different to how people _think_ about data. MongoDB is easier on the brain, that way.

angilly 16y ago
> Don't get me wrong, I love MongoDB, but there are much better reasons to use MongoDB, such as the fact that every document is a flexible data structure, not a strict collection of columns. You can add keys and values as you choose, and store them as arrays or sub-documents depending on the encapsulation you need, etc. Yup. I dropped the ball on this one. Should have a list of 4 reasons. :)
aaronblohowiak 16y ago
You are using de-normalization in a way that i am no familiar with: http://en.wikipedia.org/wiki/Database_normalization http://en.wikipedia.org/wiki/Database_normalization