Mika
Mika

Reputation: 5845

Mongo index for query

I have a collection with millions of records. I am trying to implement an autocomplete on a field called term that I broke down into an array of words called words. My query is very slow because I am missing something with regards to the index. Can someone please help?

I have the following query:

db.vx.find({ 
    semantic: "product", 
    concept: true, 
    active: true, 
    $and: [ { words: { $regex: "^doxycycl.*" } } ] 
}).sort({ length: 1 }).limit(100).explain()

The explain output says that no index was used even though I have the following index:

{
    "v" : 1,
    "key" : {
        "words" : 1,
        "active" : 1,
        "concept" : 1,
        "semantic" : 1
    },
    "name" : "words_1_active_1_concept_1_semantic_1",
    "ns" : "mydatabase.vx"
}

Upvotes: 0

Views: 53

Answers (1)

alfredopacino
alfredopacino

Reputation: 3241

You can check if the compound index is exploited correctly using the mongo shell db.vx.find({YOURQUERY}).explain('executionStats')

and check the field winningPlan.stage:

  • COLLSCAN means the indexes are partially used or not used at all.
  • IXSCAN means the indexes are used correctly in this query.

You can also check if the text search fits your needs since is way more fast than $regex operator. https://comsysto.com/blog-post/mongodb-full-text-search-vs-regular-expressions

Upvotes: 1

Related Questions