Create an index for specific query

Question

could you please help me to solve following problem:

I have a query which scans a collection to find the result, I would like to create an index (or maybe something else) in order to improve the execution speed.

Here is the SQL equivalent of the query

active=true 
AND 
exclude_from_search=false 
AND 
(
    full_name_lc like '%buttor% 
    OR 
    user_name_lc like '%button%' 
    OR 
    first_name_lc like '%button%' 
    OR last_name_lc like '%button%'
) 
AND 
group !='Star'

Here is the MongoDB query:

db.user.find({ 
    "active":true,
    "exclude_from_search":false,
    $or:[
        {"full_name_lc":{$regex:"button"}},
        {"user_name_lc":{$regex:"button"}},
        {"first_name_lc":{$regex:"button"}},
        {"last_name_lc":{$regex:"button"}}
    ],
    "group":{$ne:"Star"}
})

Thanks you in advance.

I would put the or at the last place. First match everything that is easy active, exclude_from_search and group. Also put indexes on those. The regexes is not that easy to do as far as I know — MKroeders
– MKroeders, Commented Oct 24, 2014 at 13:38
Do I get this right that you store natural and lower case versions of user_name,full_name,last_name and first_name (the last 3 holding a redundancy anyway)? If yes, eliminating those fields and using /button/i as a case insensitive search over the "parent" fields makes much more sense... — Markus W Mahlberg
– Markus W Mahlberg, Commented Oct 24, 2014 at 15:14

Wizard · Accepted Answer · 2014-10-24 14:03:25Z

Perhaps making a compound index is enough.

db.user.ensureIndex({active : 1, exclude_from_search : 1, group : 1}, {name : "aeg"});

db.user.find({ 
    "active":true,
    "exclude_from_search":false,
    $or:[
        {"full_name_lc":{$regex:"button"}},
        {"user_name_lc":{$regex:"button"}},
        {"first_name_lc":{$regex:"button"}},
        {"last_name_lc":{$regex:"button"}}
    ],
    "group":{$ne:"Star"}
}).explain();

{
    "cursor" : "BtreeCursor aeg",
    "isMultiKey" : false,
    "n" : 0,
    "nscannedObjects" : 0,
    "nscanned" : 0,
    "nscannedObjectsAllPlans" : 0,
    "nscannedAllPlans" : 0,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
            "active" : [
                    [
                            true,
                            true
                    ]
            ],
            "exclude_from_search" : [
                    [
                            false,
                            false
                    ]
            ],
            "group" : [
                    [
                            {
                                    "$minElement" : 1
                            },
                            "Star"
                    ],
                    [
                            "Star",
                            {
                                    "$maxElement" : 1
                            }
                    ]
            ]
    },
    "server" : "xxx",
    "filterSet" : false
}

Sergiu Velescu · Accepted Answer · 2015-11-16 18:43:02Z

0

This is a well known problem - in case you use wild-cast search on both sides (... like '%some_string%') you have no other choose than FULL TABLE SCAN (this is a well known problem - you can not create an index to be used in this cases, you will have to rethink the logic)

answered Nov 16, 2015 at 18:43

Sergiu Velescu

5243 silver badges9 bronze badges

Collectives™ on Stack Overflow

Create an index for specific query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related