MongoDB index strategy for BSON `_id` vs String `id`?
up vote
0
down vote
favorite
I have a question for the scholars.
2018-11-09T19:01:39.896+0100 [conn1851] query database.collection
query: {
$query: {
id: "5bb79a18293609212200cbe2"
},
$orderby: {
_id: 1
}
}
planSummary: IXSCAN { _id: 1 }
ntoskip:0
nscanned:138476
nscannedObjects:138476
keyUpdates:0
numYields:0
locks(micros)
r:127627
nreturned:1
reslen:497
127ms
To me it looks like because I am not querying the _id with BSON then the index isn't used properly. Do I really have to create additional indexes for querying the database on the string value of the _id namely id or is it just the type mismatch or something?
These are the current indexes I have
> db.collection.getIndexes()
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "database.collection"
},
{
"v" : 1,
"key" : {
"sport_id" : 1
},
"name" : "sport_id_1",
"ns" : "database.collection"
},
{
"v" : 1,
"key" : {
"sport_id" : 1,
"updated_at" : -1
},
"name" : "sport_id_1_updated_at_-1",
"ns" : "database.collection"
},
{
"v" : 1,
"key" : {
"external_ids.id" : 1
},
"name" : "external_ids.id_1",
"ns" : "database.collection"
}
]
I am currently on mongodb 2.6.12 and using mongoid v 2.5 i think.
This is part of what a document looks like:
{
"_id" : ObjectId("593a61de2936093a460004ca"),
"sport_id" : ObjectId("592eefe3293609345c000867")
"id" : "593a61de2936093a460004ca",
"created_at" : ISODate("2017-06-09T08:52:46Z")
}
Looks like all our documents are also saving the string representation of the _id. This is just horribly wrongly modeled huh?
I didn't create this mess but I will have to fix it. Any suggestions how to proceed from this? Looks like we are for unknown reasons duplicating the _id into an id. Should I just index that or is there a better fix? I might have to rewrite this question come to think of it...
mongodb mongoid
asked Nov 9 at 18:09
mhenrixon
4,44143362
add a comment |
up vote
0
down vote
favorite
I have a question for the scholars.
2018-11-09T19:01:39.896+0100 [conn1851] query database.collection
query: {
$query: {
id: "5bb79a18293609212200cbe2"
},
$orderby: {
_id: 1
}
}
planSummary: IXSCAN { _id: 1 }
ntoskip:0
nscanned:138476
nscannedObjects:138476
keyUpdates:0
numYields:0
locks(micros)
r:127627
nreturned:1
reslen:497
127ms
To me it looks like because I am not querying the _id with BSON then the index isn't used properly. Do I really have to create additional indexes for querying the database on the string value of the _id namely id or is it just the type mismatch or something?
These are the current indexes I have
> db.collection.getIndexes()
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "database.collection"
},
{
"v" : 1,
"key" : {
"sport_id" : 1
},
"name" : "sport_id_1",
"ns" : "database.collection"
},
{
"v" : 1,
"key" : {
"sport_id" : 1,
"updated_at" : -1
},
"name" : "sport_id_1_updated_at_-1",
"ns" : "database.collection"
},
{
"v" : 1,
"key" : {
"external_ids.id" : 1
},
"name" : "external_ids.id_1",
"ns" : "database.collection"
}
]
I am currently on mongodb 2.6.12 and using mongoid v 2.5 i think.
This is part of what a document looks like:
{
"_id" : ObjectId("593a61de2936093a460004ca"),
"sport_id" : ObjectId("592eefe3293609345c000867")
"id" : "593a61de2936093a460004ca",
"created_at" : ISODate("2017-06-09T08:52:46Z")
}
Looks like all our documents are also saving the string representation of the _id. This is just horribly wrongly modeled huh?
I didn't create this mess but I will have to fix it. Any suggestions how to proceed from this? Looks like we are for unknown reasons duplicating the _id into an id. Should I just index that or is there a better fix? I might have to rewrite this question come to think of it...
mongodb mongoid
asked Nov 9 at 18:09
mhenrixon
4,44143362
I'm not clear on what you're doing. Do your documents have a separateidfield?
– JohnnyHK
Nov 9 at 18:17
Looks like it @JohnnyHK... How do I fix this mess?
– mhenrixon
Nov 9 at 20:02
Ideally you'd remove theidfield from all your documents and stick to using_ideverywhere instead.
– JohnnyHK
Nov 9 at 21:14
1
Filtering by 'id' then sorting by '_id' implies 'id' is not unique. Is it correct? Before removing 'id' check if it is being used as a string anywhere, e.g. regexp or any other string manipulation functions. 2.6 cannot convert types runtime so it might be the reason to have the duplicate. Even better if you dig up your VCS for the commit that introduced the string 'id' - it may explain why it is here or confirm it is sae to remove.
– Alex Blex
Nov 12 at 10:08
Thanks @AlexBlex I'll get to the bottom of this before deleting the column. For now, making sure that all queries are done to the _id column seems to do the trick!
– mhenrixon
Nov 13 at 8:14
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have a question for the scholars.
2018-11-09T19:01:39.896+0100 [conn1851] query database.collection
query: {
$query: {
id: "5bb79a18293609212200cbe2"
},
$orderby: {
_id: 1
}
}
planSummary: IXSCAN { _id: 1 }
ntoskip:0
nscanned:138476
nscannedObjects:138476
keyUpdates:0
numYields:0
locks(micros)
r:127627
nreturned:1
reslen:497
127ms
To me it looks like because I am not querying the _id with BSON then the index isn't used properly. Do I really have to create additional indexes for querying the database on the string value of the _id namely id or is it just the type mismatch or something?
These are the current indexes I have
> db.collection.getIndexes()
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "database.collection"
},
{
"v" : 1,
"key" : {
"sport_id" : 1
},
"name" : "sport_id_1",
"ns" : "database.collection"
},
{
"v" : 1,
"key" : {
"sport_id" : 1,
"updated_at" : -1
},
"name" : "sport_id_1_updated_at_-1",
"ns" : "database.collection"
},
{
"v" : 1,
"key" : {
"external_ids.id" : 1
},
"name" : "external_ids.id_1",
"ns" : "database.collection"
}
]
I am currently on mongodb 2.6.12 and using mongoid v 2.5 i think.
This is part of what a document looks like:
{
"_id" : ObjectId("593a61de2936093a460004ca"),
"sport_id" : ObjectId("592eefe3293609345c000867")
"id" : "593a61de2936093a460004ca",
"created_at" : ISODate("2017-06-09T08:52:46Z")
}
Looks like all our documents are also saving the string representation of the _id. This is just horribly wrongly modeled huh?
I didn't create this mess but I will have to fix it. Any suggestions how to proceed from this? Looks like we are for unknown reasons duplicating the _id into an id. Should I just index that or is there a better fix? I might have to rewrite this question come to think of it...
mongodb mongoid
asked Nov 9 at 18:09
mhenrixon
4,44143362
I have a question for the scholars.
2018-11-09T19:01:39.896+0100 [conn1851] query database.collection
query: {
$query: {
id: "5bb79a18293609212200cbe2"
},
$orderby: {
_id: 1
}
}
planSummary: IXSCAN { _id: 1 }
ntoskip:0
nscanned:138476
nscannedObjects:138476
keyUpdates:0
numYields:0
locks(micros)
r:127627
nreturned:1
reslen:497
127ms
To me it looks like because I am not querying the _id with BSON then the index isn't used properly. Do I really have to create additional indexes for querying the database on the string value of the _id namely id or is it just the type mismatch or something?
These are the current indexes I have
> db.collection.getIndexes()
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "database.collection"
},
{
"v" : 1,
"key" : {
"sport_id" : 1
},
"name" : "sport_id_1",
"ns" : "database.collection"
},
{
"v" : 1,
"key" : {
"sport_id" : 1,
"updated_at" : -1
},
"name" : "sport_id_1_updated_at_-1",
"ns" : "database.collection"
},
{
"v" : 1,
"key" : {
"external_ids.id" : 1
},
"name" : "external_ids.id_1",
"ns" : "database.collection"
}
]
I am currently on mongodb 2.6.12 and using mongoid v 2.5 i think.
This is part of what a document looks like:
{
"_id" : ObjectId("593a61de2936093a460004ca"),
"sport_id" : ObjectId("592eefe3293609345c000867")
"id" : "593a61de2936093a460004ca",
"created_at" : ISODate("2017-06-09T08:52:46Z")
}
Looks like all our documents are also saving the string representation of the _id. This is just horribly wrongly modeled huh?
I didn't create this mess but I will have to fix it. Any suggestions how to proceed from this? Looks like we are for unknown reasons duplicating the _id into an id. Should I just index that or is there a better fix? I might have to rewrite this question come to think of it...
mongodb mongoid
mongodb mongoid
asked Nov 9 at 18:09
mhenrixon
4,44143362
asked Nov 9 at 18:09
mhenrixon
4,44143362
edited Nov 9 at 20:01
asked Nov 9 at 18:09
mhenrixon
4,44143362
asked Nov 9 at 18:09
mhenrixon
4,44143362
asked Nov 9 at 18:09
mhenrixon
4,44143362
4,44143362
I'm not clear on what you're doing. Do your documents have a separateidfield?
– JohnnyHK
Nov 9 at 18:17
Looks like it @JohnnyHK... How do I fix this mess?
– mhenrixon
Nov 9 at 20:02
Ideally you'd remove theidfield from all your documents and stick to using_ideverywhere instead.
– JohnnyHK
Nov 9 at 21:14
1
Filtering by 'id' then sorting by '_id' implies 'id' is not unique. Is it correct? Before removing 'id' check if it is being used as a string anywhere, e.g. regexp or any other string manipulation functions. 2.6 cannot convert types runtime so it might be the reason to have the duplicate. Even better if you dig up your VCS for the commit that introduced the string 'id' - it may explain why it is here or confirm it is sae to remove.
– Alex Blex
Nov 12 at 10:08
Thanks @AlexBlex I'll get to the bottom of this before deleting the column. For now, making sure that all queries are done to the _id column seems to do the trick!
– mhenrixon
Nov 13 at 8:14
add a comment |
I'm not clear on what you're doing. Do your documents have a separateidfield?
– JohnnyHK
Nov 9 at 18:17
Looks like it @JohnnyHK... How do I fix this mess?
– mhenrixon
Nov 9 at 20:02
Ideally you'd remove theidfield from all your documents and stick to using_ideverywhere instead.
– JohnnyHK
Nov 9 at 21:14
1
Filtering by 'id' then sorting by '_id' implies 'id' is not unique. Is it correct? Before removing 'id' check if it is being used as a string anywhere, e.g. regexp or any other string manipulation functions. 2.6 cannot convert types runtime so it might be the reason to have the duplicate. Even better if you dig up your VCS for the commit that introduced the string 'id' - it may explain why it is here or confirm it is sae to remove.
– Alex Blex
Nov 12 at 10:08
Thanks @AlexBlex I'll get to the bottom of this before deleting the column. For now, making sure that all queries are done to the _id column seems to do the trick!
– mhenrixon
Nov 13 at 8:14
I'm not clear on what you're doing. Do your documents have a separate
id field?– JohnnyHK
Nov 9 at 18:17
I'm not clear on what you're doing. Do your documents have a separate
id field?– JohnnyHK
Nov 9 at 18:17
Looks like it @JohnnyHK... How do I fix this mess?
– mhenrixon
Nov 9 at 20:02
Looks like it @JohnnyHK... How do I fix this mess?
– mhenrixon
Nov 9 at 20:02
Ideally you'd remove the
id field from all your documents and stick to using _id everywhere instead.– JohnnyHK
Nov 9 at 21:14
Ideally you'd remove the
id field from all your documents and stick to using _id everywhere instead.– JohnnyHK
Nov 9 at 21:14
1
1
Filtering by 'id' then sorting by '_id' implies 'id' is not unique. Is it correct? Before removing 'id' check if it is being used as a string anywhere, e.g. regexp or any other string manipulation functions. 2.6 cannot convert types runtime so it might be the reason to have the duplicate. Even better if you dig up your VCS for the commit that introduced the string 'id' - it may explain why it is here or confirm it is sae to remove.
– Alex Blex
Nov 12 at 10:08
Filtering by 'id' then sorting by '_id' implies 'id' is not unique. Is it correct? Before removing 'id' check if it is being used as a string anywhere, e.g. regexp or any other string manipulation functions. 2.6 cannot convert types runtime so it might be the reason to have the duplicate. Even better if you dig up your VCS for the commit that introduced the string 'id' - it may explain why it is here or confirm it is sae to remove.
– Alex Blex
Nov 12 at 10:08
Thanks @AlexBlex I'll get to the bottom of this before deleting the column. For now, making sure that all queries are done to the _id column seems to do the trick!
– mhenrixon
Nov 13 at 8:14
Thanks @AlexBlex I'll get to the bottom of this before deleting the column. For now, making sure that all queries are done to the _id column seems to do the trick!
– mhenrixon
Nov 13 at 8:14
add a comment |
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53231202%2fmongodb-index-strategy-for-bson-id-vs-string-id%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53231202%2fmongodb-index-strategy-for-bson-id-vs-string-id%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I'm not clear on what you're doing. Do your documents have a separate
idfield?– JohnnyHK
Nov 9 at 18:17
Looks like it @JohnnyHK... How do I fix this mess?
– mhenrixon
Nov 9 at 20:02
Ideally you'd remove the
idfield from all your documents and stick to using_ideverywhere instead.– JohnnyHK
Nov 9 at 21:14
1
Filtering by 'id' then sorting by '_id' implies 'id' is not unique. Is it correct? Before removing 'id' check if it is being used as a string anywhere, e.g. regexp or any other string manipulation functions. 2.6 cannot convert types runtime so it might be the reason to have the duplicate. Even better if you dig up your VCS for the commit that introduced the string 'id' - it may explain why it is here or confirm it is sae to remove.
– Alex Blex
Nov 12 at 10:08
Thanks @AlexBlex I'll get to the bottom of this before deleting the column. For now, making sure that all queries are done to the _id column seems to do the trick!
– mhenrixon
Nov 13 at 8:14