0

I have 145359 Documents stored in a local MongoDB, which I am trying to index. To speed up my query.

{
  "Categorized": true
}

Sample Document

{
    "_id" : "dbe14c04-bd1e-454a-af57-4ca61566c0c0",
    "Categorized" : true,
    "Archived" : false,
    "HasTorrent" : false,
    "TorrentFile" : null,
    "File" : {
        "TitlePattern" : null,
        "Extension" : ".mp4",
        "FileName" : "A Costume for Nicholas (Un Disfraz para Nicolas) (2020) 1080p WEBRip.mp4",
        "FileNameWithoutExtension" : "A Costume for Nicholas (Un Disfraz para Nicolas) (2020) 1080p WEBRip",
        "AdapterName" : "CandyBD",
        "AdapterNameLower" : "candybd",
        "Type" : 1,
        "SupportedISPs" : [ 
            "dotinternet"
        ]
    },
    "IsSaved" : false,
    "IsUpdated" : true,
    "MovieId" : "tt8045396",
    "Quality" : 16,
    "UpdateComplete" : true,
    "WatchCount" : 0,
    "Is3D" : false,
    "FileFoundDateTime" : ISODate("2022-01-03T02:12:03.236Z"),
    "InformationDateTime" : ISODate("2022-01-03T02:12:11.553Z"),
    "Dimension" : "1080p",
    "IsDubbed" : false
}

The question is when trying to index the collection by

{
    "Categorized" : 1
}

According to compass Documents Returned: 94057 and Documents Examined: 94057 from 145359 before indexing, great. But the thing is before indexing Actual Query Execution Time (ms):95 (around) and the CollScan Execution Time was 0-11 on average 4 ms. But after indexing Actual Query Execution Time (ms):124 Fetch was around 9 ms and Ixscan was around 5 ms.

What does this value mean and why does it look like my query took longer to execute after indexing?

Taufiq Abdur Rahman
  • 1,348
  • 4
  • 24
  • 44

1 Answers1

0

You have 145359 docs in a collection. Boolean fields are not particularly good candidates for indexing when T/F distribution approaches 50/50, and in this case, you have 94057 docs (65%) that match true. An index is really not going to materially help you here.

Buzz Moschetti
  • 7,057
  • 3
  • 23
  • 33
  • But as far as I found Full scan or Collscan which scans all the 145359 documents are not recommended? – Taufiq Abdur Rahman Jan 12 '22 at 04:42
  • 1
    As the number of documents are not many, indexing may not be of use (150,000 is not a large amount of documents). And, as already mentioned in the answer above, boolean fields do not make good candidates as indexed fields. Also, see this topic on [Create Queries that Ensure Selectivity](https://docs.mongodb.com/manual/tutorial/create-queries-that-ensure-selectivity/). I can have a collection with few thousands of documents and no indexes. All my queries will have collection scans - no issues with that. – prasad_ Jan 12 '22 at 05:21