Mongo index for query

Question

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"
}

- I don't get how your field is callled term if you say and query for a field called words. — alfredopacino
– alfredopacino, Commented Apr 12, 2017 at 13:43
Words is an array that contains each word in the filed term as term is a sentence — Mika
– Mika, Commented Apr 12, 2017 at 13:44
So there is no term field. Your document have an array of string words and that's all, am I right? — alfredopacino
– alfredopacino, Commented Apr 12, 2017 at 13:47
There is a term field but I don't query against it because if the search is multiple words from the term field but in the wrong order I can no longer search against it. — Mika
– Mika, Commented Apr 12, 2017 at 13:49
I can't reproduce this; when I set up a collection with these fields and this index, and run this query, it says that there was a "FETCH" stage followed by an "IXSCAN" stage. There must be something different in our setups, I suppose. Could you show in more detail what your data looks like, and what the explain() returns? — Vince Bowdren
– Vince Bowdren, Commented Apr 12, 2017 at 15:00

alfredopacino · Accepted Answer · 2017-04-12 23:46:09Z

1

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

edited Apr 12, 2017 at 23:46

answered Apr 12, 2017 at 13:59

alfredopacino

3,3419 gold badges48 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mongo index for query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related