Creating index on number field quater wise

Working with Data
,
#1

i have a below document.
{
“_id” : {
“date” : NumberLong(“1645488000000”),
“course_type” : “Library”,
“browser” : “Safari”,
“device” : “desktop”,

},

},

i have created index
“key” : {
“_id.date” : 1
},
“name” : “_id.date_1”,

“indexSize” : 88653824,

whenever i run below query it takes long time if the date number is older.
.aggregate([
{
$match: {
‘_id.date’: { ‘$gte’: 1484870400000, ‘$lte’: 1640995199000 },

can i create a index like , depending on the number
like if the number is between 1484870400000 and 1640995199000. create a index and accordingly

#2

Hi @Kishore_Ohal welcome to the community!

can i create a index like , depending on the number
like if the number is between 1484870400000 and 1640995199000. create a index and accordingly

Yes you can with a partial index. A partial index would allow you to set conditions on what gets indexed or not.

However I think the main issue is that if you match a large enough number of documents, the query becomes slow because the number of documents became larger than your memory, thus MongoDB must fetch more documents from disk. Fetching a large number of data from disk is typically slow.

I have a couple of questions though:

  1. You have multiple fields inside the _id field, making the _id field a subdocument. Is there a reason for this design? Note that the _id field is automatically uniquely indexed, but having it as a subdocument is tricky, since field ordering matters. E.g., for an indexed subdocument, {a:1, b:2} is not the same as {b:2, a:1} even though they contain fields with identical values. This may create confusion unless you’re absolutely sure that the field order will stay consistent no matter what (which in many programming languages e.g. Python, there may not be such a guarantee), and you may inadvertently allowing duplicate documents into the collection.
  2. To determine if it is disk access that’s slowing down your query, what’s the output of the explain plan of the query? Typically a stage with a high number of needYield means that it’s waiting for something slow, e.g. disk access. Could you post the explain output of the slow query?

Finally, what is your MongoDB version?

Best regards
Kevin

1 Like