Mongo DB Index Issue

[Shankar freelancer]

why Mongo DB only take one index at a time then its bad for multiple filter together placed, because if a site that have 100+ filter to search result then we have to create a set of compound index combination that matches with the filter combination...its not a smart way . i using this in a project and almost 3 days gone but performance issue still remain.

please take a look about my issue and reply me ASAP because i totally stuck for this issue

Thanks

[Rhys Campbell]

You need to give more info. From what you describe this is not really a MongoDB specific issue but more a schema design issue. A second possibility, if the fields are all strings is a text index, https://docs.mongodb.com/v3.2/core/index-text/

You should post a few example documents, queries and a description of what you are trying to achieve. You'll then be able to get specific advice.

[Shankar freelancer]

Actually i have both strings and integer fields for filter thats why i don't used text index.

for example my query is like this -

db.runCommand({ aggregate: "scrapedannounces", pipeline: [ { $match: { 'basic.selectedPropertyType': "Residential", 'basic.propertyOperation': "buy",     'location.lng': { $gte: -1.494136742187493, $lte: 5.921634742187507 },      'location.lat': { $gte: 44.0266926170229, $lte: 48.34374558290405 }, status: "ACTIVE",      $and: [ { $or: [ { 'furnish.furnishType': "Bare" }, { 'furnish.furnishType': "Unfurnished" },      { 'furnish.furnishType': "Semi Furnish" }, { 'furnish.furnishType': "Furnish" } ] },     { $or: [ { userType: "Agency" } ] } ] } },{"$sort":{"created" : 1}}]})

 and when extra filter add into this -

db.runCommand({ aggregate: "scrapedannounces", pipeline: [ { $match: { 'basic.selectedPropertyType': "Residential", 'basic.propertyOperation': "buy",     'location.lng': { $gte: -1.494136742187493, $lte: 5.921634742187507 },      'location.lat': { $gte: 44.0266926170229, $lte: 48.34374558290405 }, status: "ACTIVE", 'basic.price': { '$gte': 0, '$lte': 2013 },'basic.surface': { '$gte': 0, '$lte': 2013 },     $and: [ { $or: [ { 'furnish.furnishType': "Bare" }, { 'furnish.furnishType': "Unfurnished" },      { 'furnish.furnishType': "Semi Furnish" }, { 'furnish.furnishType': "Furnish" } ] },     { $or: [ { userType: "Agency" } ] } ] } },{"$sort":{"created" : 1}}]})

this is my one of compound index that have worked for only 4 filter and if i use another extra filter in my query then this indexed don't used.

db.scrapedannounces.createIndex({"basic.price": 1,"basic.priceRatio": 1,"basic.surface": 1,"basic.bedrooms": 1,},{sparse:true})

so if user placed all filter at a time then i have to create a combination of all possible filter placed by user and create index as well according this, thats my issue 

[Rhys Campbell]

Include some sample documents.

[Shankar freelancer]

this is my json document and i have 10M data like this

{

    "_id" : ObjectId("58d4d49ebf01c0f390efe621"),

    "scrapId" : "8QwT980909420223",

    "basic" : {

        "updatePriceDate" : "2017-03-24T09:09:42.386Z",

        "termAccepted" : 1,

        "termsCount" : 2,

        "long_term" : 2681,

        "termPriceCheck" : {

            "longTerm" : true

        },

        "priceExchangeValue" : 1.0,

        "priceRatio" : 17.19,

        "price" : 2681,

        "surface" : 156,

        "bedrooms" : 5,

        "availability" : "2017-07-05T09:09:42.388Z",

        "propertyOperation" : "rent",

        "selectedMeasurementValue" : 1,

        "selectedMeasurementType" : "M2",

        "selectedCurrency" : "EUR €",

        "selectedResidentialType" : {

            "name" : "Apartment",

            "id" : 1

        },

        "selectedAvailability" : "from",

        "selectedPropertyType" : "Residential",

        "termParameterLong" : "month"

    },

    "location" : {

        "latLng" : [ 

            5.689956, 

            44.962435

        ],

        "latLngCommute" : [ 

            5.689956, 

            44.962435

        ],

        "address" : "unknown",

        "lng" : 5.689956,

        "lat" : 44.962435,

        "userCity" : "Lyon",

        "userCountry" : "France",

        "isPreciseLocation" : true

    },

    "created" : "2017-03-24T09:09:42.223Z",

    "updated" : "2017-03-24T09:09:42.223Z",

    "userType" : "Agency",

    "announceType" : "External Announce",

    "status" : "ACTIVE",

    "building" : {

        "parkingCount" : 1,

        "has_parking" : "true",

        "cave_size" : 15,

        "temp_parking_spots" : [ 

            {

                "included" : "Yes"

            }

        ],

        "building_height" : 3,

        "building_age" : 2016,

        "has_roofTop" : false,

        "has_laundry" : false,

        "has_doorman" : true,

        "easy_handicap" : true,

        "has_meeting" : true,

        "has_gym" : false,

        "has_garden" : false,

        "has_pool" : false,

        "has_cave" : true,

        "has_elevator" : false,

        "selectedBuildingAge" : "0"

    },

    "property" : {

        "rooms_total" : 10,

        "selectedEnergyConsumption" : {

            "name" : "C (91 to 150kWh/m2 /yr)",

            "id" : 3

        },

        "property_levels" : 1,

        "selectedPropertyStatus" : {

            "name" : "Good_Conditions",

            "id" : 2

        },

        "penthouse" : true,

        "floor_nr" : 3,

        "bathrooms" : 1

    },

    "interior" : {

        "fullBathroomCount" : 1

    },

    "furnish" : {

        "furnishType" : "Unfurnish",

        "has_wardrobe" : true,

        "has_kitchen_cupboards" : true,

        "has_mattress" : true,

        "has_beds" : true

    },

    "rent" : {

        "securityCost" : "1",

        "property_charges" : 134.0

    },

    "media" : {

        "mediaCount" : 1,

        "imageArray" : [ 

            {

                "caption" : "ev1f",

                "imgPath" : "C0QpFyqg-ImageHouse-2.jpg"

            }, 

            {

                "caption" : "zi8e",

                "imgPath" : "nNHj8UKz-Mr6JvFIR-background-image-967820_960_720.jpg"

            }

        ]

    },

    "contact" : {

        "contactPhone" : "111111111",

        "contactName" : "testName"

    }

}

[Rhys Campbell]

Can you modify your data model to something like this?

{
 "name": "Test Property",
 "attributes": [
 { "name": "furnish.furnishType", "value": "Unfurnished" },
 { "name": "basic.selectedPropertyType", "value": "Residential" },
 { "name": "basic.gardenSize", "value": "Large" }
 { "name": "basic.bedrooms", "value": 1 },
 { "name": "basic.price", "value": 1 },
 { "name": "basic.bathrooms", "value": 1 }
 ]
}

You can then create an index like so...

db.collection.createIndex({ "attributes.name": 1, "attributes.value": 1 });

and then query like so...

db.collection.find({ "attributes.name": "basic.selectedPropertyType",  "value": "Residential", "furnish.furnishType", "value": "Unfurnished" })

[Shankar freelancer]

 First of all thanks for quick reply, actually for now i can't change it because if this changed then we have to  recreate many module according  to current db changes and that we cant do at this stage ,so please give some other  alternative solutions.

[Asya Kamsky]

You don't necessarily need to change your schema, however, you *do*
need to understand/specify which fields will always be used for
queries.

Your given example uses a lot of fields in the filter:

'basic.selectedPropertyType': "Residential", '
basic.propertyOperation': "buy",
'location.lng': { $gte: -1.494136742187493, $lte: 5.921634742187507 },
'location.lat': { $gte: 44.0266926170229, $lte: 48.34374558290405 },
status: "ACTIVE",
$and: [
{ $or: [ { 'furnish.furnishType': "Bare" }, {
'furnish.furnishType': "Unfurnished" }, { 'furnish.furnishType':
"Semi Furnish" }, { 'furnish.furnishType': "Furnish" } ] },
{ $or: [ { userType: "Agency" } ] }
]

Are there fields that are always required for the search? property
type? property operation? status?
Are there ones that are usually included?
Are there fields that are added by the system always?

Those are are always there and that are equality should be the leading
field(s) of the index.
Those that are ranges or are sorted by should be after/last in the index.

You absolutely should expect to need to create multiple indexes if
there are many combinations that you allow searching/filtering by.

And if you want to use geospatial queries it may be more efficient to
create 2dsphere index, but without knowing how frequently searches are
bounded by area vs other parameters, it's hard to say.

It also appears the furnishType enumerates all possible values - is
there any reason to even include it in the query at all?

Asya

> --
> You received this message because you are subscribed to the Google Groups
> "mongodb-user"
> group.
>
> For other MongoDB technical support options, see:
> https://docs.mongodb.com/manual/support/
> ---
> You received this message because you are subscribed to the Google Groups
> "mongodb-user" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to mongodb-user...@googlegroups.com.
> To post to this group, send email to mongod...@googlegroups.com.
> Visit this group at https://groups.google.com/group/mongodb-user.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/mongodb-user/9e881048-e1ed-4688-9459-32ac59939383%40googlegroups.com.
>
> For more options, visit https://groups.google.com/d/optout.



--
Asya Kamsky
Lead Product Manager
MongoDB
Download MongoDB - mongodb.org/downloads
We're Hiring! - https://www.mongodb.com/careers

[Shankar freelancer]

yes these fields like property type ,property operation ,status, lat,lng are my default filter in find query

 and some time furnish type also used in default filter. so first i made a compound index that have those default field including sort filed in last. These filter worked with location lat lng boundaries .

so according to you , i have to create compound indexes like first used default field and then other combination fields 

 for example if i used other filter with in find query like this

'basic.selectedPropertyType': "Residential", ' 
basic.propertyOperation': "buy", 
'location.lng': { $gte: -1.494136742187493, $lte: 5.921634742187507 }, 
'location.lat': { $gte: 44.0266926170229, $lte: 48.34374558290405 }, 
status: "ACTIVE", 

'basic.price': { '$gte': 2000 },

'basic.surface': { '$gte': 164 },

'basic.bedrooms': { '$gte': 1 },

$and: [ 
       { $or: [ { 'furnish.furnishType': "Bare" }, { 
'furnish.furnishType': "Unfurnished" },      { 'furnish.furnishType': 
"Semi Furnish" }, { 'furnish.furnishType': "Furnish" } ] } 

] 

so i have to to create compound index like this ? - 

db.scrapedannounces.createIndex (

{

    "basic.selectedPropertyType" : 1,

    "basic.propertyOperation" : 1,

    "location.lng" : 1,

    "location.lat" : 1,

    "status" : 1,

    'basic.price': 1,

    'basic.surface': 1,

    'basic.bedrooms': 1,

    "furnish.furnishType" : 1,

    "created" : -1

},

{sparse:true}

)

created is sorted field

also i have read in docs that only 64 indexes limit for per collection so if i create indexes with combination then it will be cross the 64 limit

Thanks

[Rhys Campbell]

As Asya pointed out you probably don't need to index all possible combinations. It's likely many queries will be perfectly fine with a subset of the fields indexed. Create a set of indexes you think will be best, turn on profling, and check the results. Any results with a high ratio between totalKeysExamined and nReturned may mean you want to to modify your set of indexes.

You may want to look at loading your data into a specialised search server. Solr and elasticsearch are ones I've used for similar stuff.

[Asya Kamsky]

As Rhys said, you don't need to index every combination.

You *do* want to have equality field like status before the range
query fields, like location.* etc.

Also, get rid of "sparse" option from the index definition, it's not
doing anything for you here.

> --
> You received this message because you are subscribed to the Google Groups
> "mongodb-user"
> group.
>
> For other MongoDB technical support options, see:
> https://docs.mongodb.com/manual/support/
> ---
> You received this message because you are subscribed to the Google Groups
> "mongodb-user" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to mongodb-user...@googlegroups.com.
> To post to this group, send email to mongod...@googlegroups.com.
> Visit this group at https://groups.google.com/group/mongodb-user.
> To view this discussion on the web visit

> https://groups.google.com/d/msgid/mongodb-user/a1c3faf4-c7d1-4ad8-b1cc-269d925c537c%40googlegroups.com.

[Shankar freelancer]

Thanks, now the performance seems good. can you tell me that if i search first time then it will take time but after that second time same filter gives me fast result is indexing work with cache??

Regards

[Rhys Campbell]

exactly.

[Shankar freelancer]

so mean indexing take lot of memory and working in RAM right?

[Rhys Campbell]

"a lot" -> depends on how big it is. See https://docs.mongodb.com/manual/reference/command/dbStats/

[Shankar freelancer]

Thanks Rhys for your great help and support