0

I have a collection in MongoDB where the documents have a structure like this:

{
    "vechicleDetails": {
        "location": {
            "type": "Point",
            "coordinate": [78.00, -40] //longitude, latitude
        },
        "city": "New Delhi"
    },
    "searchPriority": 4,
    "isLive": true,
    "vehicleCondition": "USED",
    "dateOfGoingLive": ISODate()
}

There are lots of other fields as well but I have omitted them because they are not relevant for this question.

I have a query which will be executed very frequently on this collection:

db.vehicleinventories_latlong.find({ $query: {isLive: false, vehicleCondition: "USED", "vehicleDetails.city": {$in: ["New Delhi", "Delhi", "Gurugram"]}, "vehicleDetails.location": { $nearSphere: { $geometry: { type: "Point", coordinates: [ 77.2, 28.6 ] }, $maxDistance: 100 * 1000 } }}, $orderby: {searchPriority: -1, dateOfGoingLive: -1} })

With this query I am trying to fetch all documents which meet the following criteria:

  • isLive: false
  • vehicleCondition: "USED"
  • vehicleDetails.city is one of these values: New Delhi, Delhi, Gurugram,
  • vehicleDetails.location is within 100kms radius of [ 77.2, 28.6 ] (longitude, latitude in that order)

I want the results to be sorted first by distance from [ 77.2, 28.6 ], then by searchPriority and then by dateOfGoingLive. To make these queries faster, I have created a compound index on: isLive, vehicleCondition, vehicleDetails.location (of type 2dsphere), searchPriority and dateOfGoingLive.

When I execute this explain command:

db.vehicleinventories_latlong.find({ $query: {isLive: false, vehicleCondition: "USED", "vehicleDetails.city": {$in: ["New Delhi", "Delhi", "Gurugram"]}, "vehicleDetails.location": { $nearSphere: { $geometry: { type: "Point", coordinates: [ 77.2, 28.6 ] }, $maxDistance: 100 * 1000 } }}, $orderby: {searchPriority: -1, dateOfGoingLive: -1} }).explain()

This is just the previous query command with .explain() at the end. This is the output I get on MongoShell:

https://pastebin.com/qEvUNju0 (i have removed some parts of the output because it did not fit the 512K limit for pastebin)

Its quite long and I dont know how to interpret these results. I can see that the index is being used but why am I seeing so many vehicleDetails.location array of tuples? I currently have just 360 documents in my collection and only 15 documents satisfy the criteria of this query. Have I indexed the collection correctly for this query?

This query will be exectued in response to a web request which will ask for "all LIVE, USED vehicles from cities: New Delhi, Delhi and Gurugram sorted first by distance from New Delhi, then by searchPriority and then by date of going live". The order in which I have listed city names inside the $in clause in my query is relevant. The distance has to be measured from the first city inside the $in clause. This means that the $nearsphere clause in the query will have coordinates of the first city inside $in array.

In the collection, vehicleDetails.location coordinates will be the same for all vehicles belonging to the same city and the number of cities in my DB is quite low. With these details, do you think 2dsphere index is helping this query? How can I make these queries faster?

EDIT: Added output of getIndexKeys():

db.vehicleinventories_latlong.getIndexKeys()
[ { _id: 1 },
  { isLive: 1,
    vehicleCondition: 1,
    'vehicleDetails.location': '2dsphere' },
  { isLive: 1,
    vehicleCondition: 1,
    'vehicleDetails.location': '2dsphere',
    searchPriority: 1 },
  { isLive: 1,
    vehicleCondition: 1,
    'vehicleDetails.location': '2dsphere',
    searchPriority: 1,
    dateOfGoingLive: 1 },
  { isLive: 1,
    vehicleCondition: 1,
    'vehicleDetails.city': 1,
    'vehicleDetails.location': '2dsphere',
    searchPriority: -1,
    dateOfGoingLive: -1 } ]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.