Can MongoDB index be null?

Adam Fields

Table of Contents Show

Can an index be NULL?
Are NULL values allowed in MongoDB?
What is the default value of MongoDB index?
Which index Cannot be deleted in MongoDB?

2011-01-14 03:22:04 UTC

Permalink

Is it correct that queries for null values won't use an index?

For example, let's say I have an index on attributes a, b, and c. I want to find documents where b is true and a and c are null. It appears that if I do db.collection.find({a:null, b:true, c:null}), this does a table scan (it's orders of magnitude slower than the same query with values for a and c).

Is there any way to do this more efficiently?

Thanks.

--
- Adam
----------
If you liked this email, you might also like:
"Feedback to Skype on the new v5 beta"
-- http://workstuff.tumblr.com/post/2638681448
"My Huck Finn edit"
-- http://www.aquick.org/blog/2011/01/08/my-huck-finn-edit/
"Homemade Danish"
-- http://www.flickr.com/photos/fields/5310547424/
"fields: More guns != more safety. http://t.co/wxrGov5"
-- http://twitter.com/fields/statuses/25729178028482560
----------
** I design intricate-yet-elegant processes for user and machine problems.
** Custom development project broken? Contact me, I can help.
** Some of what I do: http://workstuff.tumblr.com/post/70505118/aboutworkstuff

[ http://www.adamfields.com/resume.html ].. Experience
[ http://www.morningside-analytics.com ] .. Latest Venture
[ http://www.confabb.com ] ................ Founder

--
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To post to this group, send email to mongodb-user-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
To unsubscribe from this group, send email to mongodb-user+***@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/mongodb-user?hl=en.

Nat

2011-01-14 03:29:14 UTC

Permalink

It should be indexed unless it's a sparse index in 1.7. What are your indexes and field histogram look like?
-----Original Message-----
From: Adam Fields <fields-91+YnXXLsFtWk0Htik3J/***@public.gmane.org>
Sender: mongodb-user-/***@public.gmane.org
Date: Thu, 13 Jan 2011 22:22:04
To: mongodb-user<mongodb-user-/***@public.gmane.org>
Reply-To: mongodb-user-/***@public.gmane.org
Subject: [mongodb-user] null values and indexes

Is it correct that queries for null values won't use an index?

For example, let's say I have an index on attributes a, b, and c. I want to find documents where b is true and a and c are null. It appears that if I do db.collection.find({a:null, b:true, c:null}), this does a table scan (it's orders of magnitude slower than the same query with values for a and c).

Is there any way to do this more efficiently?

Thanks.

--
- Adam
----------
If you liked this email, you might also like:
"Feedback to Skype on the new v5 beta"
-- http://workstuff.tumblr.com/post/2638681448
"My Huck Finn edit"
-- http://www.aquick.org/blog/2011/01/08/my-huck-finn-edit/
"Homemade Danish"
-- http://www.flickr.com/photos/fields/5310547424/
"fields: More guns != more safety. http://t.co/wxrGov5"
-- http://twitter.com/fields/statuses/25729178028482560
----------
** I design intricate-yet-elegant processes for user and machine problems.
** Custom development project broken? Contact me, I can help.
** Some of what I do: http://workstuff.tumblr.com/post/70505118/aboutworkstuff

[ http://www.adamfields.com/resume.html ].. Experience
[ http://www.morningside-analytics.com ] .. Latest Venture
[ http://www.confabb.com ] ................ Founder
--
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To post to this group, send email to mongodb-user-/JYPxA39Uh5TLH3MbocFF+G/***@public.gmane.org
To unsubscribe from this group, send email to mongodb-user+***@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/mongodb-user?hl=en.

Adam Fields

2011-01-14 03:29:44 UTC

Permalink

Is it correct that queries for null values won't use an index?
For example, let's say I have an index on attributes a, b, and c. I want to find documvalue1ts where b is true and a and c are null. It appears that if I do db.collection.find({a:null, b:true, c:null}), this does a table scan (it's orders of magnitude slower than the same query with values for a and c).
Is there any way to do this more efficivalue1tly?
db.collection.find({'a' : null, 'b': true, 'c': null}).explain()

{
"cursor" : "BtreeCursor a_1_b_1_c_1",
"nscanned" : 2821726,
"nscannedObjects" : 2821726,
"n" : 2821726,
"millis" : 228530,
"indexBounds" : {
"a" : [
[
null,
null
]
],
"b" : [
[
true,
true
]
],
"c" : [
[
null,
null
]
]
}
}

db.collection.find({'a' : "value1", 'b': true, 'c': "value2"}).explain()

{
"cursor" : "BtreeCursor a_1_b_1_c_1",
"nscanned" : 44645,
"nscannedObjects" : 44645,
"n" : 44645,
"millis" : 3474,
"indexBounds" : {
"a" : [
[
"value1",
"value1"
]
],
"b" : [
[
true,
true
]
],
"c" : [
[
"value2",
"value2"
]
]
}
}

Nat

2011-01-14 03:35:07 UTC

Permalink

Both cases use the same cursor. Just in the first case, it returns 2
million records while the second case returns only 44,000 records. How
could you expect it to use the same amount of time?

db.collection.find({'a' : "value1", 'b': true, 'c': "value2"}).explain()

Adam Fields

2011-01-14 03:44:47 UTC

Permalink

Both cases use the same cursor. Just in the first case, it returns 2
million records while the second case returns only 44,000 records. How
could you expect it to use the same amount of time?

Hmm. I guess I was mistaken. I thought nscannedObjects was the number of objects considered for matching, not the number of objects that did match.

db.collection.find({'a' : "value1", 'b': true, 'c': "value2"}).explain()

--
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
For more options, visit this group at http://groups.google.com/group/mongodb-user?hl=en.

Nat

2011-01-14 03:46:33 UTC

Permalink

You are not mistaken. Look at "n" instead.
-----Original Message-----
From: Adam Fields <fields-91+YnXXLsFtWk0Htik3J/***@public.gmane.org>
Sender: mongodb-user-/***@public.gmane.org
Date: Thu, 13 Jan 2011 22:44:47
To: <mongodb-user-/***@public.gmane.org>
Reply-To: mongodb-user-/***@public.gmane.org
Subject: Re: [mongodb-user] Re: null values and indexes

Both cases use the same cursor. Just in the first case, it returns 2
million records while the second case returns only 44,000 records. How
could you expect it to use the same amount of time?

Hmm. I guess I was mistaken. I thought nscannedObjects was the number of objects considered for matching, not the number of objects that did match.

db.collection.find({'a' : "value1", 'b': true, 'c': "value2"}).explain()

--
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
For more options, visit this group at http://groups.google.com/group/mongodb-user?hl=en.

Adam Fields

2011-01-14 03:52:04 UTC

Permalink

You are not mistaken. Look at "n" instead.

But then why is nscannedObjects different (and the same as each respective n) for these two queries with the same structure?

-----Original Message-----
Date: Thu, 13 Jan 2011 22:44:47
Subject: Re: [mongodb-user] Re: null values and indexes

Both cases use the same cursor. Just in the first case, it returns 2
million records while the second case returns only 44,000 records. How
could you expect it to use the same amount of time?

Hmm. I guess I was mistaken. I thought nscannedObjects was the number of objects considered for matching, not the number of objects that did match.

db.collection.find({'a' : "value1", 'b': true, 'c': "value2"}).explain()

--
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
For more options, visit this group at http://groups.google.com/group/mongodb-user?hl=en.

--
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
For more options, visit this group at http://groups.google.com/group/mongodb-user?hl=en.
--
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
For more options, visit this group at http://groups.google.com/group/mongodb-user?hl=en.

Nat

2011-01-14 03:57:47 UTC

Permalink

Because data distribution is different. Your data is not equally distributed. Check the number of returned result and you'll see.
-----Original Message-----
From: Adam Fields <fields-91+YnXXLsFtWk0Htik3J/***@public.gmane.org>
Sender: mongodb-user-/***@public.gmane.org
Date: Thu, 13 Jan 2011 22:52:04
To: <mongodb-user-/***@public.gmane.org>
Reply-To: mongodb-user-/***@public.gmane.org
Subject: Re: [mongodb-user] Re: null values and indexes

You are not mistaken. Look at "n" instead.

But then why is nscannedObjects different (and the same as each respective n) for these two queries with the same structure?

-----Original Message-----
Date: Thu, 13 Jan 2011 22:44:47
Subject: Re: [mongodb-user] Re: null values and indexes

Both cases use the same cursor. Just in the first case, it returns 2
million records while the second case returns only 44,000 records. How
could you expect it to use the same amount of time?

Hmm. I guess I was mistaken. I thought nscannedObjects was the number of objects considered for matching, not the number of objects that did match.

db.collection.find({'a' : "value1", 'b': true, 'c': "value2"}).explain()

--
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
For more options, visit this group at http://groups.google.com/group/mongodb-user?hl=en.

Can an index be NULL?

By default, relational databases ignore NULL values (because the relational model says that NULL means "not present"). So, Index does not store NULL value, consequently if you have null condition in SQL statement, related index is ignored (by default).

Are NULL values allowed in MongoDB?

Indeed, it's not possible to store null values in a MongoDB document using a DataFrame. The Python None values are considered as missing attributes accordingly to this NoSQL specific allowance.

What is the default value of MongoDB index?

MongoDB will index the first occurrence of a key and delete all subsequent documents from the collection that contain a duplicate value for that key. The default value is false .

Which index Cannot be deleted in MongoDB?

When creating documents in a collection, MongoDB creates a unique index using the _id field. MongoDB refers to this as the Default _id Index. This default index cannot be dropped from the collection.