Monday, December 3, 2012

Grumpy Old Man Plays the Role of Query Optimizer

Another in the continuing saga of MongoDB. As you maye gleaned from other posts, I am yet to become an all in fan of the product. I have some appreciation of its capabilities, but still am finding it syntactically and semantically hard going.
The class from the 10gen team is very well done. a few glitches, but nothing major. I have been on expensive classes where the material has been of lesser quality.
So kudos to 10gen - especially Andrew.
The strange bits. And this week there are 2. First off there is thing called the "aggregation pipeline". Cool concept, you specify as steps in a pipeline what to do with the data, so you can do things like group, sort, and generally report usefully on the data contents. Cleverly (of course), the results from each stage of an execution pipeline are mongo (JSON) documents. So you can operate on them just like any others. Nice.
But, in order to do quite complicated queries, you have to specify every step of the pipeline yourself. And you have to figure out the order you perform them in(to get the right results and to optimize the performance). If I sort before I filter, then I am probably doing too much work, for example. In SQL databases - at least the better versions, the query optimizer is supposed to figure this out for you. So, niow I am having to be my own query optimizer. Not happy about that. Yeah there are reasons, sharding might be tricky to optimize (I don't know).
The second - and this is quite uncomfortable feling, but I dare say I will get used to it is referencing. This is a bit tricky so I will illustrate (I hope correctly!)
if I want to group by the value of category, where category is a key in a document,
I would have to right something like {"group":{"_id":"$category"....}}

I read this as make the _id field the value obtained from the category key. Each one in quotes (programming in strings again, uh), and then the $ to dereference the name of the category key so I can use its value. That's an awful lot of symbology to remember.

Grumpiness quotient has gone up this week!