I have three queries to get needed data by group by currency and price/profit.
One query takes ~1.3 seconds on 230 000 items.
One item looks like this:
{
"_id" : ObjectId("590e59fca0404a6e5577302b"),
"make_name" : "Peugeot",
"model_name" : "307",
"car_id" : NumberInt("396554354"),
"title" : "Sell",
"description" : "Cool",
"site_name" : "olx.ua",
"first_registration" : ISODate("2002-01-01T00:00:00.000Z"),
"fuel" : "Petrol",
"mileage" : NumberInt("250000"),
"category" : "Limousine",
"horse_power" : null,
"cubic_capacity" : NumberInt("1600"),
"transmission" : "Manual",
"price" : NumberInt("5050"),
"currency" : "USD",
"negotiable" : true,
"profit" : NumberInt("-8"),
"owners_count" : NumberInt("2"),
"color" : NumberInt("3"),
"condition" : NumberInt("4"),
"updated_at" : ISODate("2017-06-01T03:51:34.000Z"),
"rear_camera" : false,
"ABS" : false,
"four_wheel_drive" : false,
"bluetooth" : false,
"board_computer" : false,
"cd_player" : false,
"electric_mirrors" : false,
"electric_windows" : true,
"parking_assistance" : false,
"handsfree" : false,
"guarantee" : false,
"head_up_display" : false,
"has_inspection" : false,
"air_conditioning" : false,
"alloy_wheel_rims" : false,
"multi_func_steering_wheel" : false,
"navigation" : false,
"non_smoking_car" : false,
"panorama_roof" : false,
"particle_filter" : false,
"rain_sensor" : false,
"full_service_history" : false,
"power_steering" : false,
"sunroof" : false,
"seat_heating" : false,
"sports_suspension" : false,
"sports_seats" : false,
"pre_heating" : false,
"start_stop" : false,
"taxi" : false,
"tax_paid" : true,
"cruise_control" : false,
"xenon_headlights" : true,
"security" : false,
"sport_package" : false,
"business" : true,
"damaged" : false,
"price_100" : 5000,
"profit_100" : 0
},
My query is:
db.cars.aggregate([{
'$match': {
'$and': [
{ 'first_registration': { '$gte': ISODate("2000-01-01") } },
{ 'first_registration': { '$lte': ISODate("2017-01-01") } },
{ 'price': { '$gte': 0 } },
{ 'price': { '$lte': 60000 } },
{ 'profit': { '$exists': true } },
{ 'profit': { '$gte': -20000 } },
{ 'profit': { '$lte': 30000 } },
{ 'updated_at': { '$gte': ISODate("2017-06-04") } },
{ 'currency': 'USD' },
{ 'damaged': false }]
}
},
{
'$group': {
'_id': {
'price': {
'$subtract': ['$price',
{ '$mod': ['$price', 100] }]
},
'profit': { '$subtract': ['$profit', { '$mod': ['$profit', 100] }] }
},
'car_id': { '$first': '$car_id' },
'currency': { '$first': '$currency' },
'price': { '$first': '$price' },
'profit': { '$first': '$profit' }
}
}])
I need to get first item in a group of specified price/profit.
Example: 10 cars have price/profit 100-160 USD, so only one car will be returned for such query, because group(data) point for this car is price 100, profit 100. I hope this works this way.
First "match" query takes around 0.012 seconds to get 150 000 items.
So the issue is in group query, I think.
I tried to pre-build math operations subtract and mod:
db.cars.find({
'profit': {'$exists': true},
'price_100': {'$exists': false}, }).snapshot().forEach(function(doc){
db.cars.update({_id:doc._id}, {$set:{
"price_100":doc.price - (doc.price % 100),
"profit_100": doc.profit - (doc.profit % 100)
}});
});
Then my query started to look like:
db.cars.aggregate(
[
{
'$match': {
'$and': [
{ 'first_registration': { '$gte': ISODate("2000-01-01") } },
{ 'first_registration': { '$lte': ISODate("2017-01-01") } },
{ 'price': { '$gte': 0 } },
{ 'price': { '$lte': 60000 } },
{ 'profit': { '$exists': true } },
{ 'profit': { '$gte': -20000 } },
{ 'profit': { '$lte': 30000 } },
{ 'updated_at': { '$gte': ISODate("2017-06-04") } },
{ 'currency': 'USD' },
{ 'damaged': false }]
}
},
{
'$group': {
'_id': {
'price': '$price_100',
'profit': '$profit_100',
},
'car_id': { '$first': '$car_id' },
'currency': { '$first': '$currency' },
'price': { '$first': '$price' },
'profit': { '$first': '$profit' }
}
}])
Unfortunately, it takes 300 milliseconds more than original.
Explain to my query:
{
"stages" : [
{
"$cursor" : {
"query" : {
"$and" : [
{
"first_registration" : {
"$gte" : ISODate("2000-01-01T00:00:00.000Z")
}
},
{
"first_registration" : {
"$lte" : ISODate("2017-01-01T00:00:00.000Z")
}
},
{
"price" : {
"$gte" : 0
}
},
{
"price" : {
"$lte" : 60000
}
},
{
"profit" : {
"$exists" : true
}
},
{
"profit" : {
"$gte" : -20000
}
},
{
"profit" : {
"$lte" : 30000
}
},
{
"updated_at" : {
"$gte" : ISODate("2017-06-04T00:00:00.000Z")
}
},
{
"currency" : "USD"
},
{
"damaged" : false
}
]
},
"fields" : {
"car_id" : NumberInt("1"),
"currency" : NumberInt("1"),
"price" : NumberInt("1"),
"price_100" : NumberInt("1"),
"profit" : NumberInt("1"),
"profit_100" : NumberInt("1"),
"_id" : NumberInt("0")
},
"queryPlanner" : {
"plannerVersion" : NumberInt("1"),
"namespace" : "master_test.cars",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"currency" : {
"$eq" : "USD"
}
},
{
"damaged" : {
"$eq" : false
}
},
{
"first_registration" : {
"$lte" : ISODate("2017-01-01T00:00:00.000Z")
}
},
{
"price" : {
"$lte" : 60000
}
},
{
"profit" : {
"$lte" : 30000
}
},
{
"first_registration" : {
"$gte" : ISODate("2000-01-01T00:00:00.000Z")
}
},
{
"price" : {
"$gte" : 0
}
},
{
"profit" : {
"$gte" : -20000
}
},
{
"updated_at" : {
"$gte" : ISODate("2017-06-04T00:00:00.000Z")
}
},
{
"profit" : {
"$exists" : true
}
}
]
},
"winningPlan" : {
"stage" : "COLLSCAN",
"filter" : {
"$and" : [
{
"currency" : {
"$eq" : "USD"
}
},
{
"damaged" : {
"$eq" : false
}
},
{
"first_registration" : {
"$lte" : ISODate("2017-01-01T00:00:00.000Z")
}
},
{
"price" : {
"$lte" : 60000
}
},
{
"profit" : {
"$lte" : 30000
}
},
{
"first_registration" : {
"$gte" : ISODate("2000-01-01T00:00:00.000Z")
}
},
{
"price" : {
"$gte" : 0
}
},
{
"profit" : {
"$gte" : -20000
}
},
{
"updated_at" : {
"$gte" : ISODate("2017-06-04T00:00:00.000Z")
}
},
{
"profit" : {
"$exists" : true
}
}
]
},
"direction" : "forward"
},
"rejectedPlans" : [ ]
}
}
},
{
"$group" : {
"_id" : {
"price" : "$price_100",
"profit" : "$profit_100"
},
"car_id" : {
"$first" : "$car_id"
},
"currency" : {
"$first" : "$currency"
},
"price" : {
"$first" : "$price"
},
"profit" : {
"$first" : "$profit"
}
}
}
],
"ok" : 1
}
Why 3 queries one might think? I have 3 currencies: USD, EUR and PLN in my data base, so I do 3 requests. Currently I don't know how to unify the query.
UPDATE FOR NEIL:
After implementing your suggestions, I was able to reduce time from 1.3 seconds to 1 second.
Query looks like:
db.cars.aggregate([{
'$match': {
'$and': [
{ 'first_registration': { '$gte': ISODate("2000-01-01"), '$lte': ISODate("2017-01-01") } },
{ 'price': { '$gte': 0, '$lte': 60000 } },
{ 'profit': { '$exists': true, '$gte': -20000, '$lte': 30000 } },
{ 'updated_at': { '$gte': ISODate("2017-06-04") } },
{ 'currency': 'USD' },
{ 'damaged': false }]
}
},
{
'$group': {
'_id': {
'price': {
'$subtract': ['$price',
{ '$mod': ['$price', 100] }]
},
'profit': { '$subtract': ['$profit', { '$mod': ['$profit', 100] }] }
},
'car_id': { '$first': '$car_id' },
'currency': { '$first': '$currency' },
'price': { '$first': '$price' },
'profit': { '$first': '$profit' }
}
}])
And explain:
{
"stages" : [
{
"$cursor" : {
"query" : {
"$and" : [
{
"first_registration" : {
"$gte" : ISODate("2000-01-01T00:00:00.000Z"),
"$lte" : ISODate("2017-01-01T00:00:00.000Z")
}
},
{
"price" : {
"$gte" : 0,
"$lte" : 60000
}
},
{
"profit" : {
"$exists" : true,
"$gte" : -20000,
"$lte" : 30000
}
},
{
"updated_at" : {
"$gte" : ISODate("2017-06-04T00:00:00.000Z")
}
},
{
"currency" : "USD"
},
{
"damaged" : false
}
]
},
"fields" : {
"car_id" : NumberInt("1"),
"currency" : NumberInt("1"),
"price" : NumberInt("1"),
"profit" : NumberInt("1"),
"_id" : NumberInt("0")
},
"queryPlanner" : {
"plannerVersion" : NumberInt("1"),
"namespace" : "master_test.cars",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"currency" : {
"$eq" : "USD"
}
},
{
"damaged" : {
"$eq" : false
}
},
{
"first_registration" : {
"$lte" : ISODate("2017-01-01T00:00:00.000Z")
}
},
{
"price" : {
"$lte" : 60000
}
},
{
"profit" : {
"$lte" : 30000
}
},
{
"first_registration" : {
"$gte" : ISODate("2000-01-01T00:00:00.000Z")
}
},
{
"price" : {
"$gte" : 0
}
},
{
"profit" : {
"$gte" : -20000
}
},
{
"updated_at" : {
"$gte" : ISODate("2017-06-04T00:00:00.000Z")
}
},
{
"profit" : {
"$exists" : true
}
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"first_registration" : {
"$lte" : ISODate("2017-01-01T00:00:00.000Z")
}
},
{
"price" : {
"$lte" : 60000
}
},
{
"profit" : {
"$lte" : 30000
}
},
{
"first_registration" : {
"$gte" : ISODate("2000-01-01T00:00:00.000Z")
}
},
{
"price" : {
"$gte" : 0
}
},
{
"profit" : {
"$gte" : -20000
}
},
{
"profit" : {
"$exists" : true
}
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"updated_at" : 1,
"currency" : 1,
"damaged" : 1
},
"indexName" : "updated_at_1_currency_1_damaged_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"updated_at" : [ ],
"currency" : [ ],
"damaged" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : NumberInt("2"),
"direction" : "forward",
"indexBounds" : {
"updated_at" : [
"[new Date(1496534400000), new Date(9223372036854775807)]"
],
"currency" : [
"[\"USD\", \"USD\"]"
],
"damaged" : [
"[false, false]"
]
}
}
},
"rejectedPlans" : [ ]
}
}
},
{
"$group" : {
"_id" : {
"price" : {
"$subtract" : [
"$price",
{
"$mod" : [
"$price",
{
"$const" : 100
}
]
}
]
},
"profit" : {
"$subtract" : [
"$profit",
{
"$mod" : [
"$profit",
{
"$const" : 100
}
]
}
]
}
},
"car_id" : {
"$first" : "$car_id"
},
"currency" : {
"$first" : "$currency"
},
"price" : {
"$first" : "$price"
},
"profit" : {
"$first" : "$profit"
}
}
}
],
"ok" : 1
}
Running on pre-built fields price_100 and profit_100 still 1.3 seconds, but now we have 300 ms less for non-prebuilt query, nice!