MongoDB counting with index is slower than counting without index
0
I have created a Mongo collection with 1,505,000 documents.
When there is no index on a key, and I am doing this:
db.test.count({a: {$ne: "some_string"}})
it takes approx to 1200ms to return the amount.
When I am doing the same thing, but for this time WITH index on a key, it takes approx to 12000ms.
Technical details
Index was created using:
db.test.createIndex({a: 1})For equivalence, when using
findinstead ofcountit provides with results after 0.002 ms.I am using MongoDB on my localhost.
This is the executionStats:
/* 1 */
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "db.test",
"indexFilterSet" : false,
"parsedQuery" : {
"$nor" : [
{
"a" : {
"$eq" : "some_string"
}
}
]
},
"winningPlan" : {
"stage" : "COUNT",
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"a" : 1.0
},
"indexName" : "a_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"a" :
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"a" : [
"[MinKey, "some_string")",
"("some_string", MaxKey]"
]
}
}
}
},
"rejectedPlans" :
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 0,
"executionTimeMillis" : 10318,
"totalKeysExamined" : 1505000,
"totalDocsExamined" : 1505000,
"executionStages" : {
"stage" : "COUNT",
"nReturned" : 0,
"executionTimeMillisEstimate" : 9916,
"works" : 1505001,
"advanced" : 0,
"needTime" : 1505000,
"needYield" : 0,
"saveState" : 12102,
"restoreState" : 12102,
"isEOF" : 1,
"invalidates" : 0,
"nCounted" : 1505000,
"nSkipped" : 0,
"inputStage" : {
"stage" : "FETCH",
"nReturned" : 1505000,
"executionTimeMillisEstimate" : 9736,
"works" : 1505001,
"advanced" : 1505000,
"needTime" : 0,
"needYield" : 0,
"saveState" : 12102,
"restoreState" : 12102,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 1505000,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 1505000,
"executionTimeMillisEstimate" : 2479,
"works" : 1505001,
"advanced" : 1505000,
"needTime" : 0,
"needYield" : 0,
"saveState" : 12102,
"restoreState" : 12102,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"a" : 1.0
},
"indexName" : "a_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"a" :
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"a" : [
"[MinKey, "some_string")",
"("some_string", MaxKey]"
]
},
"keysExamined" : 1505000,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
}
},
"serverInfo" : {
"host" : "razs-MacBook",
"port" : 27017,
"version" : "3.4.6",
"gitVersion" : ""
},
"ok" : 1.0
}
What's the deal?
mongodb indexing nosql counting
asked Nov 24 '18 at 12:44
RazRaz
281112
add a comment |
0
I have created a Mongo collection with 1,505,000 documents.
When there is no index on a key, and I am doing this:
db.test.count({a: {$ne: "some_string"}})
it takes approx to 1200ms to return the amount.
When I am doing the same thing, but for this time WITH index on a key, it takes approx to 12000ms.
Technical details
Index was created using:
db.test.createIndex({a: 1})For equivalence, when using
findinstead ofcountit provides with results after 0.002 ms.I am using MongoDB on my localhost.
This is the executionStats:
/* 1 */
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "db.test",
"indexFilterSet" : false,
"parsedQuery" : {
"$nor" : [
{
"a" : {
"$eq" : "some_string"
}
}
]
},
"winningPlan" : {
"stage" : "COUNT",
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"a" : 1.0
},
"indexName" : "a_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"a" :
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"a" : [
"[MinKey, "some_string")",
"("some_string", MaxKey]"
]
}
}
}
},
"rejectedPlans" :
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 0,
"executionTimeMillis" : 10318,
"totalKeysExamined" : 1505000,
"totalDocsExamined" : 1505000,
"executionStages" : {
"stage" : "COUNT",
"nReturned" : 0,
"executionTimeMillisEstimate" : 9916,
"works" : 1505001,
"advanced" : 0,
"needTime" : 1505000,
"needYield" : 0,
"saveState" : 12102,
"restoreState" : 12102,
"isEOF" : 1,
"invalidates" : 0,
"nCounted" : 1505000,
"nSkipped" : 0,
"inputStage" : {
"stage" : "FETCH",
"nReturned" : 1505000,
"executionTimeMillisEstimate" : 9736,
"works" : 1505001,
"advanced" : 1505000,
"needTime" : 0,
"needYield" : 0,
"saveState" : 12102,
"restoreState" : 12102,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 1505000,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 1505000,
"executionTimeMillisEstimate" : 2479,
"works" : 1505001,
"advanced" : 1505000,
"needTime" : 0,
"needYield" : 0,
"saveState" : 12102,
"restoreState" : 12102,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"a" : 1.0
},
"indexName" : "a_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"a" :
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"a" : [
"[MinKey, "some_string")",
"("some_string", MaxKey]"
]
},
"keysExamined" : 1505000,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
}
},
"serverInfo" : {
"host" : "razs-MacBook",
"port" : 27017,
"version" : "3.4.6",
"gitVersion" : ""
},
"ok" : 1.0
}
What's the deal?
mongodb indexing nosql counting
asked Nov 24 '18 at 12:44
RazRaz
281112
use $explain to check weather your query is using index or not. Also you will be able to debug with the help of explain
– Shumi Gupta
Nov 24 '18 at 14:58
@ShumiGupta I have edited my question adding the explain.
– Raz
Nov 24 '18 at 15:55
add a comment |
0
0
0
I have created a Mongo collection with 1,505,000 documents.
When there is no index on a key, and I am doing this:
db.test.count({a: {$ne: "some_string"}})
it takes approx to 1200ms to return the amount.
When I am doing the same thing, but for this time WITH index on a key, it takes approx to 12000ms.
Technical details
Index was created using:
db.test.createIndex({a: 1})For equivalence, when using
findinstead ofcountit provides with results after 0.002 ms.I am using MongoDB on my localhost.
This is the executionStats:
/* 1 */
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "db.test",
"indexFilterSet" : false,
"parsedQuery" : {
"$nor" : [
{
"a" : {
"$eq" : "some_string"
}
}
]
},
"winningPlan" : {
"stage" : "COUNT",
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"a" : 1.0
},
"indexName" : "a_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"a" :
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"a" : [
"[MinKey, "some_string")",
"("some_string", MaxKey]"
]
}
}
}
},
"rejectedPlans" :
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 0,
"executionTimeMillis" : 10318,
"totalKeysExamined" : 1505000,
"totalDocsExamined" : 1505000,
"executionStages" : {
"stage" : "COUNT",
"nReturned" : 0,
"executionTimeMillisEstimate" : 9916,
"works" : 1505001,
"advanced" : 0,
"needTime" : 1505000,
"needYield" : 0,
"saveState" : 12102,
"restoreState" : 12102,
"isEOF" : 1,
"invalidates" : 0,
"nCounted" : 1505000,
"nSkipped" : 0,
"inputStage" : {
"stage" : "FETCH",
"nReturned" : 1505000,
"executionTimeMillisEstimate" : 9736,
"works" : 1505001,
"advanced" : 1505000,
"needTime" : 0,
"needYield" : 0,
"saveState" : 12102,
"restoreState" : 12102,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 1505000,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 1505000,
"executionTimeMillisEstimate" : 2479,
"works" : 1505001,
"advanced" : 1505000,
"needTime" : 0,
"needYield" : 0,
"saveState" : 12102,
"restoreState" : 12102,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"a" : 1.0
},
"indexName" : "a_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"a" :
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"a" : [
"[MinKey, "some_string")",
"("some_string", MaxKey]"
]
},
"keysExamined" : 1505000,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
}
},
"serverInfo" : {
"host" : "razs-MacBook",
"port" : 27017,
"version" : "3.4.6",
"gitVersion" : ""
},
"ok" : 1.0
}
What's the deal?
mongodb indexing nosql counting
asked Nov 24 '18 at 12:44
RazRaz
281112
I have created a Mongo collection with 1,505,000 documents.
When there is no index on a key, and I am doing this:
db.test.count({a: {$ne: "some_string"}})
it takes approx to 1200ms to return the amount.
When I am doing the same thing, but for this time WITH index on a key, it takes approx to 12000ms.
Technical details
Index was created using:
db.test.createIndex({a: 1})For equivalence, when using
findinstead ofcountit provides with results after 0.002 ms.I am using MongoDB on my localhost.
This is the executionStats:
/* 1 */
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "db.test",
"indexFilterSet" : false,
"parsedQuery" : {
"$nor" : [
{
"a" : {
"$eq" : "some_string"
}
}
]
},
"winningPlan" : {
"stage" : "COUNT",
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"a" : 1.0
},
"indexName" : "a_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"a" :
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"a" : [
"[MinKey, "some_string")",
"("some_string", MaxKey]"
]
}
}
}
},
"rejectedPlans" :
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 0,
"executionTimeMillis" : 10318,
"totalKeysExamined" : 1505000,
"totalDocsExamined" : 1505000,
"executionStages" : {
"stage" : "COUNT",
"nReturned" : 0,
"executionTimeMillisEstimate" : 9916,
"works" : 1505001,
"advanced" : 0,
"needTime" : 1505000,
"needYield" : 0,
"saveState" : 12102,
"restoreState" : 12102,
"isEOF" : 1,
"invalidates" : 0,
"nCounted" : 1505000,
"nSkipped" : 0,
"inputStage" : {
"stage" : "FETCH",
"nReturned" : 1505000,
"executionTimeMillisEstimate" : 9736,
"works" : 1505001,
"advanced" : 1505000,
"needTime" : 0,
"needYield" : 0,
"saveState" : 12102,
"restoreState" : 12102,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 1505000,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 1505000,
"executionTimeMillisEstimate" : 2479,
"works" : 1505001,
"advanced" : 1505000,
"needTime" : 0,
"needYield" : 0,
"saveState" : 12102,
"restoreState" : 12102,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"a" : 1.0
},
"indexName" : "a_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"a" :
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"a" : [
"[MinKey, "some_string")",
"("some_string", MaxKey]"
]
},
"keysExamined" : 1505000,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
}
},
"serverInfo" : {
"host" : "razs-MacBook",
"port" : 27017,
"version" : "3.4.6",
"gitVersion" : ""
},
"ok" : 1.0
}
What's the deal?
mongodb indexing nosql counting
mongodb indexing nosql counting
asked Nov 24 '18 at 12:44
RazRaz
281112
asked Nov 24 '18 at 12:44
RazRaz
281112
edited Nov 24 '18 at 15:55
Raz
asked Nov 24 '18 at 12:44
RazRaz
281112
asked Nov 24 '18 at 12:44
RazRaz
281112
asked Nov 24 '18 at 12:44
RazRaz
281112
281112
use $explain to check weather your query is using index or not. Also you will be able to debug with the help of explain
– Shumi Gupta
Nov 24 '18 at 14:58
@ShumiGupta I have edited my question adding the explain.
– Raz
Nov 24 '18 at 15:55
add a comment |
use $explain to check weather your query is using index or not. Also you will be able to debug with the help of explain
– Shumi Gupta
Nov 24 '18 at 14:58
@ShumiGupta I have edited my question adding the explain.
– Raz
Nov 24 '18 at 15:55
use $explain to check weather your query is using index or not. Also you will be able to debug with the help of explain
– Shumi Gupta
Nov 24 '18 at 14:58
use $explain to check weather your query is using index or not. Also you will be able to debug with the help of explain
– Shumi Gupta
Nov 24 '18 at 14:58
@ShumiGupta I have edited my question adding the explain.
– Raz
Nov 24 '18 at 15:55
@ShumiGupta I have edited my question adding the explain.
– Raz
Nov 24 '18 at 15:55
add a comment |
0
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',
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
});
}
});
draft saved
draft discarded
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%2f53458279%2fmongodb-counting-with-index-is-slower-than-counting-without-index%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53458279%2fmongodb-counting-with-index-is-slower-than-counting-without-index%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
use $explain to check weather your query is using index or not. Also you will be able to debug with the help of explain
– Shumi Gupta
Nov 24 '18 at 14:58
@ShumiGupta I have edited my question adding the explain.
– Raz
Nov 24 '18 at 15:55