Need advice on the need to have index on boolean field

[Павел Климашкин]

Hello!

I have map with hundreds of thousands photos on it. And, accordingly, has a collection of photos in Mongo.

But sometimes user (owner of the photo or admin) can deactivate photo, that it could be seen only by the owner and that it is not selected from database for all other.

For this case i have boolean field 'disabled' in photo's collection.

Therefore, for all public requests I use condition like this:

Photos.find( { "loc": { "$within": { "$box": [ [0, 0] , [3, 3] ] } } , "disabled": {$ne: true}} )

Disabled photos in base about 1%. That is because one percent of photos for the remaining 99% I have to use the condition of all queries.

How do you think, do I need to use the index on the field 'disabled'? Or for such case can be found more elegant solution?

[Tobias Trelle]

Why not use this trick (not restricted to MongoDB):

Use two collections

Photos.Private
Photos.Public

and drop the disabled field from your documents. The collection the document belongs to represents the state then. Changing the state means to move document from one collection to the other.

No index needed at all.

HTH
Tobias

[Sam Millman]

A negator on a boolean field will be hell for any database, including MongoDB due to low selectivity. You can do this better by not using the negator for one and just checking for false since with 1% selectivity it should get quite fast without an index.

So yea I would try without index and negator.

--
You received this message because you are subscribed to the Google
Groups "mongodb-user" group.
To post to this group, send email to mongod...@googlegroups.com
To unsubscribe from this group, send email to
mongodb-user...@googlegroups.com
See also the IRC channel -- freenode.net#mongodb

[Павел Климашкин]

Tobias Trelle, I also thought about different collections. But in reality, the photos have not one boolean flag.

It have states: 

'new' - Photo created, but not filled with data by user

'disabled' - deactivated by owner or admin

'del' - delete, visible only for admin

And if any of this flags set to 'true', photo not visible by all users, except owner (he see 'new' and 'disabled') and admin (see all states).

It seems necessary to have at least three collections:

Photos.Public

Photos.Private

Photos.Del

I think, is this correct decision?

пятница, 28 декабря 2012 г., 18:08:46 UTC+4 пользователь Tobias Trelle написал:

[Павел Климашкин]

Sammaye, I purposely did field value for all public photos not 'fase'. I exactly remove it for public photos, that this field does not take disc space in 99% of records.

Maby in my case, query will work faster with $exists: false, not with negator. How do You think?

пятница, 28 декабря 2012 г., 18:24:22 UTC+4 пользователь Sammaye написал:

[Sam Millman]

You prolly wanna use "disabled":null instead, that is faster than $exists. So anywhere that MongoDB picks up that the field is null or non-existant it will bring back results.


[Павел Климашкин]

Just tried to use different variants with explain() on 200 000 rows

{disabled: null} ~ 130ms

{disabled: {$ne: true}} ~ 130ms

{disabled: {$exists: false}} ~ 125ms

Something like this

суббота, 29 декабря 2012 г., 15:39:38 UTC+4 пользователь Sammaye написал:

[Sam Millman]

That's, odd normally $exists is slower, but then again maybe it's not on your data

[Павел Климашкин]

Tried it on 1 000 000 rows (20 times on each variant)

{disabled: null} ~ 505ms

{disabled: {$ne: true}} ~ 515ms

{disabled: {$exists: false}} ~ 490ms

And I noticed that $exists it is more stable, the rest more scattered over time..

суббота, 29 декабря 2012 г., 16:27:42 UTC+4 пользователь Sammaye написал: