-
Type: Bug
-
Resolution: Done
-
Priority: Minor - P4
-
Affects Version/s: 1.8.1
-
Component/s: Querying
-
Environment:ubuntu x64
Update (aaron)
A query projection a:true should be equivalent to a:1, however specification of a true value prevents covered index queries apparently due to the following line:
if ( ! e.isNumber() ) _hasNonSimple = true;
Test
c = db.c; c.drop(); c.save( { a:5 } ); assert.eq( { a:5 }, c.find( { a:5 }, { _id:0, a:1 } ).toArray()[ 0 ] ); assert.eq( { a:5 }, c.find( { a:5 }, { _id:0, a:true } ).toArray()[ 0 ] ); c.ensureIndex( { a:1 } ); assert.eq( { a:5 }, c.find( { a:5 }, { _id:0, a:1 } ).toArray()[ 0 ] ); assert( c.find( { a:5 }, { _id:0, a:1 } ).explain().indexOnly ); assert.eq( { a:5 }, c.find( {}, { _id:0, a:true } ).toArray()[ 0 ] ); // Fails because 'true' prevents doing a covered index query. assert( c.find( { a:5 }, { _id:0, a:true } ).explain().indexOnly );
------------------------------------------------------------
I'm working on a collection consisting of elements like the one below:
{
"_id" : ObjectId("4ddec579b47cc1e6024d15a9"),
"CH_price" : 0,
"TR_price" : 312,
"TW_price" : null,
"city_code" : "BER",
"currency" : "EUR",
"max_chd" : 0,
"max_chd_age" : 0,
"max_pax" : 3,
"meal" : "BB",
"min_chd" : 0,
"min_chd_age" : 0,
"min_nights" : 0,
"min_pax" : 3,
"modified" : ISODate("2011-05-26T21:24:31.871Z"),
"original_room" : "DZ",
"rate_code" : "BER466_DZ",
"room" : "DR",
"source" : "FTI",
"tlc" : "BER",
"valid_from" : ISODate("2011-05-28T00:00:00Z"),
"valid_to" : ISODate("2011-05-29T00:00:00Z"),
"hotel_name" : "EUROSTARS BERLIN",
"category" : 5,
"city_name" : "BerlĂn",
"geo" : [
52.520870208740234,
13.389501571655273
],
"name" : "Standard",
"mh_code" : "BER000AJ",
"mhr_code" : "BER000AJ01",
"hotel_id" : "BER466_CITY"
}
which has following indexes defined:
{
"name" : "id",
"ns" : "xenia_pricing.mh_rate_prices",
"key" :
},
{
"_id" : ObjectId("4d81c0f20c42d00e2c1a4e18"),
"name" : "rate_code_1_min_nights_1_valid_from_1_valid_to_1",
"ns" : "xenia_pricing.mh_rate_prices",
"key" :
}
After submitting a sample query:
db.mh_rate_prices.find({ "rate_code" :
{ "$in" : ["BER466_DZ"] }, "min_nights" :
{ "$lte" : 2 }, "$or" : [{ "valid_from" : { "$lte" :
{ "$date" : 1309071377602 }}, "valid_to" : { "$gte" :
{ "$date" : 1309071377602 }} }, { "valid_from" : { "$lte" :
{ "$date" : 1309244177602 }}, "valid_to" : { "$gte" :
{ "$date" : 1309244177602 }} }, { "valid_from" : { "$gte" :
{ "$date" : 1309071377602 }}, "valid_to" : { "$lte" :
{ "$date" : 1309244177602 }} }] }).explain()
.explain() indicated that "indexOnly" : false, which suggests that the query was not covered by index:
{
"clauses" : [
{
"cursor" : "BtreeCursor valid_from_1_rate_code_1_min_nights_1_valid_to_1",
"nscanned" : 0,
"nscannedObjects" : 0,
"n" : 0,
"millis" : 0,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" : {
"valid_from" : [
[
,
{ "$date" : 1309071377602 } ]
],
"rate_code" : [
[
"BER466_DZ",
"BER466_DZ"
]
],
"min_nights" : [
[
-1.7976931348623157e+308,
2
]
],
"valid_to" : [
[
,
{ "$maxElement" : 1 } ]
]
}
},
{
"cursor" : "BtreeCursor valid_from_1_rate_code_1_min_nights_1_valid_to_1",
"nscanned" : 0,
"nscannedObjects" : 0,
"n" : 0,
"millis" : 0,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" : {
"valid_from" : [
[
,
{ "$date" : 1309244177602 } ]
],
"rate_code" : [
[
"BER466_DZ",
"BER466_DZ"
]
],
"min_nights" : [
[
-1.7976931348623157e+308,
2
]
],
"valid_to" : [
[
,
{ "$maxElement" : 1 } ]
]
}
},
{
"cursor" : "BtreeCursor rate_code_1_min_nights_1_valid_from_1_valid_to_1",
"nscanned" : 59,
"nscannedObjects" : 0,
"n" : 0,
"millis" : 4,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" : {
"rate_code" : [
[
"BER466_DZ",
"BER466_DZ"
]
],
"min_nights" : [
[
-1.7976931348623157e+308,
2
]
],
"valid_from" : [
[
,
{ "$maxElement" : 1 } ]
],
"valid_to" : [
[
,
{ "$date" : 1309244177602 } ]
]
}
}
],
"nscanned" : 59,
"nscannedObjects" : 0,
"n" : 0,
"millis" : 4
}
What is the reason of that behaviour and how should I define indexes to avoid long response time (up to 5 seconds for some queries)?
- is duplicated by
-
SERVER-6854 query projection spec using 'true' instead of '1' prevents covered index query
- Closed
-
SERVER-10956 Queries aren't covered when using 'true' or 'false' in projection (instead of '1' or '0')
- Closed
-
SERVER-5189 Retrieving fields from a covered index doesn't use the covered index when fields are specified with _true_ instead of _1_
- Closed