Should I use sparse index for boolean flags in mongodb?
indexing, mongodb, mongoid
Solution
The sparse flag is a little weird. To understand when to use it, you have to understand why "sparse" exists in the first place.
When you create a simple index on one field, there is an entry for each document, even documents that don't have that field.
For example, if you have an index on `{rarely_set_field : 1}`, you will have an index that is filled mostly with `null` because that field doesn't exist in most cases. This is a waste of space and it's inefficient to search.
The `{sparse:true}` option will get rid of the `null` values, so you get an index that only contain entries when `{rarely_set_field}` is defined.
Back to your case.
You are asking about using a boolean + sparse. But sparse doesn't really affect "boolean", sparse affect "is set vs. is not set".
In your case, you are trying to fetch `unfinished`. To leverage `sparse` the key is not the boolean value, but the fact that `unfinished` entries have that key and that "finished" entries have no key at all.
{ _id: 1, data: {...}, unfinished: true }
{ _id: 2, data: {...} } // this entry is finished
It sounds like you are using a Queue
You can definitely leverage the information above to implement a sparse index. However, it actually sounds like you are using a Queue. MongoDB is serviceable as a Queue, here are two examples.
However, if you look at the Queue, they are not doing it the way you are doing it. I'm personally using MongoDB as a Queue for some production systems and it runs pretty well, but test your expected load as a dedicated Queue will perform much better.
Problem
I have a boolean flag `:finished`. Should I ``` A: index({ finished: 1 }) B: index({ finished: 1 }, {sparse: true}) C: use flag :unfinished instead, to query by that D: other? ``` Ruby mongoid syntax. Most my records will have flag finished=true, and most operations fetch those unfinished, obviously. I'm not sure if I understand when to use sparse and when not to. Thanks!