MongoDB oid in mysql
Let's say I have a collection of documents in MongoDb. Each has different labels. The label names are stored in MySQL and I want to have a relations table "doc-label", because every document can have many labels and every label may appear on many documents.
What would be the best way to refer to the mongo documents? Storing the OID as a string in the MySQL table would result in a pretty slow query:
SELECT *
FROM `LABEL`
INNER JOIN `DOC_LABEL` ON LABEL.id = DOC_LABEL.label_id
INNER JOIN `DOC` ON DOC.id = DOC_LABEL.doc_id
WHERE DOC.id = '507f1f77bcf86cd799439011'
mysql mongodb performance
asked Nov 16 '18 at 7:36
user3187759user3187759
85113
add a comment |
Let's say I have a collection of documents in MongoDb. Each has different labels. The label names are stored in MySQL and I want to have a relations table "doc-label", because every document can have many labels and every label may appear on many documents.
What would be the best way to refer to the mongo documents? Storing the OID as a string in the MySQL table would result in a pretty slow query:
SELECT *
FROM `LABEL`
INNER JOIN `DOC_LABEL` ON LABEL.id = DOC_LABEL.label_id
INNER JOIN `DOC` ON DOC.id = DOC_LABEL.doc_id
WHERE DOC.id = '507f1f77bcf86cd799439011'
mysql mongodb performance
asked Nov 16 '18 at 7:36
user3187759user3187759
85113
That query is invalid -- There is no tableDOC. Please fix.
– Rick James
Nov 16 '18 at 16:54
Not sure there's a "best" way. But... perhaps consider storing labels within the documents in MongoDB (even if you retain MySQL for the "system of truth" for all labels)? Seems like you're forcing an inefficient jump between database systems to resolve labels. Why not just denormalize a bit, and store your labels in the documents, to prevent extra reads & latency? And you'd also be able to index and search based on labels as well.
– David Makogon
Nov 16 '18 at 16:57
@RickJames - fixed. Thanks for pointing that out.
– user3187759
Nov 19 '18 at 20:02
@DavidMakogon - I guess you are right about the approach. Though, technically I still think there is probably a way out there to store OID efficiently in MySQL.
– user3187759
Nov 19 '18 at 20:04
1
Is507f1f77bcf86cd799439011an OID? That won't slow down MySQL significantly. Yeah, a lot of people say that strings are bad; but a BTree is a BTree. The length of the key has very little to do with performance.
– Rick James
Nov 20 '18 at 2:19
add a comment |
Let's say I have a collection of documents in MongoDb. Each has different labels. The label names are stored in MySQL and I want to have a relations table "doc-label", because every document can have many labels and every label may appear on many documents.
What would be the best way to refer to the mongo documents? Storing the OID as a string in the MySQL table would result in a pretty slow query:
SELECT *
FROM `LABEL`
INNER JOIN `DOC_LABEL` ON LABEL.id = DOC_LABEL.label_id
INNER JOIN `DOC` ON DOC.id = DOC_LABEL.doc_id
WHERE DOC.id = '507f1f77bcf86cd799439011'
mysql mongodb performance
asked Nov 16 '18 at 7:36
user3187759user3187759
85113
Let's say I have a collection of documents in MongoDb. Each has different labels. The label names are stored in MySQL and I want to have a relations table "doc-label", because every document can have many labels and every label may appear on many documents.
What would be the best way to refer to the mongo documents? Storing the OID as a string in the MySQL table would result in a pretty slow query:
SELECT *
FROM `LABEL`
INNER JOIN `DOC_LABEL` ON LABEL.id = DOC_LABEL.label_id
INNER JOIN `DOC` ON DOC.id = DOC_LABEL.doc_id
WHERE DOC.id = '507f1f77bcf86cd799439011'
mysql mongodb performance
mysql mongodb performance
asked Nov 16 '18 at 7:36
user3187759user3187759
85113
asked Nov 16 '18 at 7:36
user3187759user3187759
85113
edited Nov 19 '18 at 20:00
user3187759
asked Nov 16 '18 at 7:36
user3187759user3187759
85113
asked Nov 16 '18 at 7:36
user3187759user3187759
85113
asked Nov 16 '18 at 7:36
user3187759user3187759
85113
85113
That query is invalid -- There is no tableDOC. Please fix.
– Rick James
Nov 16 '18 at 16:54
Not sure there's a "best" way. But... perhaps consider storing labels within the documents in MongoDB (even if you retain MySQL for the "system of truth" for all labels)? Seems like you're forcing an inefficient jump between database systems to resolve labels. Why not just denormalize a bit, and store your labels in the documents, to prevent extra reads & latency? And you'd also be able to index and search based on labels as well.
– David Makogon
Nov 16 '18 at 16:57
@RickJames - fixed. Thanks for pointing that out.
– user3187759
Nov 19 '18 at 20:02
@DavidMakogon - I guess you are right about the approach. Though, technically I still think there is probably a way out there to store OID efficiently in MySQL.
– user3187759
Nov 19 '18 at 20:04
1
Is507f1f77bcf86cd799439011an OID? That won't slow down MySQL significantly. Yeah, a lot of people say that strings are bad; but a BTree is a BTree. The length of the key has very little to do with performance.
– Rick James
Nov 20 '18 at 2:19
add a comment |
That query is invalid -- There is no tableDOC. Please fix.
– Rick James
Nov 16 '18 at 16:54
Not sure there's a "best" way. But... perhaps consider storing labels within the documents in MongoDB (even if you retain MySQL for the "system of truth" for all labels)? Seems like you're forcing an inefficient jump between database systems to resolve labels. Why not just denormalize a bit, and store your labels in the documents, to prevent extra reads & latency? And you'd also be able to index and search based on labels as well.
– David Makogon
Nov 16 '18 at 16:57
@RickJames - fixed. Thanks for pointing that out.
– user3187759
Nov 19 '18 at 20:02
@DavidMakogon - I guess you are right about the approach. Though, technically I still think there is probably a way out there to store OID efficiently in MySQL.
– user3187759
Nov 19 '18 at 20:04
1
Is507f1f77bcf86cd799439011an OID? That won't slow down MySQL significantly. Yeah, a lot of people say that strings are bad; but a BTree is a BTree. The length of the key has very little to do with performance.
– Rick James
Nov 20 '18 at 2:19
That query is invalid -- There is no table
DOC. Please fix.– Rick James
Nov 16 '18 at 16:54
That query is invalid -- There is no table
DOC. Please fix.– Rick James
Nov 16 '18 at 16:54
Not sure there's a "best" way. But... perhaps consider storing labels within the documents in MongoDB (even if you retain MySQL for the "system of truth" for all labels)? Seems like you're forcing an inefficient jump between database systems to resolve labels. Why not just denormalize a bit, and store your labels in the documents, to prevent extra reads & latency? And you'd also be able to index and search based on labels as well.
– David Makogon
Nov 16 '18 at 16:57
Not sure there's a "best" way. But... perhaps consider storing labels within the documents in MongoDB (even if you retain MySQL for the "system of truth" for all labels)? Seems like you're forcing an inefficient jump between database systems to resolve labels. Why not just denormalize a bit, and store your labels in the documents, to prevent extra reads & latency? And you'd also be able to index and search based on labels as well.
– David Makogon
Nov 16 '18 at 16:57
@RickJames - fixed. Thanks for pointing that out.
– user3187759
Nov 19 '18 at 20:02
@RickJames - fixed. Thanks for pointing that out.
– user3187759
Nov 19 '18 at 20:02
@DavidMakogon - I guess you are right about the approach. Though, technically I still think there is probably a way out there to store OID efficiently in MySQL.
– user3187759
Nov 19 '18 at 20:04
@DavidMakogon - I guess you are right about the approach. Though, technically I still think there is probably a way out there to store OID efficiently in MySQL.
– user3187759
Nov 19 '18 at 20:04
1
1
Is
507f1f77bcf86cd799439011 an OID? That won't slow down MySQL significantly. Yeah, a lot of people say that strings are bad; but a BTree is a BTree. The length of the key has very little to do with performance.– Rick James
Nov 20 '18 at 2:19
Is
507f1f77bcf86cd799439011 an OID? That won't slow down MySQL significantly. Yeah, a lot of people say that strings are bad; but a BTree is a BTree. The length of the key has very little to do with performance.– Rick James
Nov 20 '18 at 2:19
add a comment |
1 Answer
1
active
oldest
votes
As @RickJames put it:
Is 507f1f77bcf86cd799439011 an OID? That won't slow down MySQL significantly. Yeah, a lot of people say that strings are bad; but a BTree is a BTree. The length of the key has very little to do with performance
answered Nov 23 '18 at 11:26
user3187759user3187759
85113
add a comment |
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',
autoActivateHeartbeat: false,
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%2f53333357%2fmongodb-oid-in-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
As @RickJames put it:
Is 507f1f77bcf86cd799439011 an OID? That won't slow down MySQL significantly. Yeah, a lot of people say that strings are bad; but a BTree is a BTree. The length of the key has very little to do with performance
answered Nov 23 '18 at 11:26
user3187759user3187759
85113
add a comment |
As @RickJames put it:
Is 507f1f77bcf86cd799439011 an OID? That won't slow down MySQL significantly. Yeah, a lot of people say that strings are bad; but a BTree is a BTree. The length of the key has very little to do with performance
answered Nov 23 '18 at 11:26
user3187759user3187759
85113
add a comment |
As @RickJames put it:
Is 507f1f77bcf86cd799439011 an OID? That won't slow down MySQL significantly. Yeah, a lot of people say that strings are bad; but a BTree is a BTree. The length of the key has very little to do with performance
answered Nov 23 '18 at 11:26
user3187759user3187759
85113
As @RickJames put it:
Is 507f1f77bcf86cd799439011 an OID? That won't slow down MySQL significantly. Yeah, a lot of people say that strings are bad; but a BTree is a BTree. The length of the key has very little to do with performance
answered Nov 23 '18 at 11:26
user3187759user3187759
85113
answered Nov 23 '18 at 11:26
user3187759user3187759
85113
answered Nov 23 '18 at 11:26
user3187759user3187759
85113
answered Nov 23 '18 at 11:26
user3187759user3187759
85113
85113
add a comment |
add a comment |
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.
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%2f53333357%2fmongodb-oid-in-mysql%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
That query is invalid -- There is no table
DOC. Please fix.– Rick James
Nov 16 '18 at 16:54
Not sure there's a "best" way. But... perhaps consider storing labels within the documents in MongoDB (even if you retain MySQL for the "system of truth" for all labels)? Seems like you're forcing an inefficient jump between database systems to resolve labels. Why not just denormalize a bit, and store your labels in the documents, to prevent extra reads & latency? And you'd also be able to index and search based on labels as well.
– David Makogon
Nov 16 '18 at 16:57
@RickJames - fixed. Thanks for pointing that out.
– user3187759
Nov 19 '18 at 20:02
@DavidMakogon - I guess you are right about the approach. Though, technically I still think there is probably a way out there to store OID efficiently in MySQL.
– user3187759
Nov 19 '18 at 20:04
1
Is
507f1f77bcf86cd799439011an OID? That won't slow down MySQL significantly. Yeah, a lot of people say that strings are bad; but a BTree is a BTree. The length of the key has very little to do with performance.– Rick James
Nov 20 '18 at 2:19