[SERVER-3156] query projection spec using 'true' instead of '1' prevents covered index query Created: 27/May/11 Updated: 12/Jul/16 Resolved: 20/Nov/13 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Querying |
| Affects Version/s: | 1.8.1 |
| Fix Version/s: | 2.5.5 |
| Type: | Bug | Priority: | Minor - P4 |
| Reporter: | Sebastian Styka | Assignee: | Benety Goh |
| Resolution: | Done | Votes: | 2 |
| Labels: | indexing, neweng, query_triage | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Environment: |
ubuntu x64 |
||
| Attachments: |
|
||||||||||||||||
| Issue Links: |
|
||||||||||||||||
| Participants: | |||||||||||||||||
| Description |
|
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:
Test
------------------------------------------------------------ I'm working on a collection consisting of elements like the one below: { which has following indexes defined: { }, } 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: { , { "$date" : 1309071377602 } ] , { "$maxElement" : 1 } ] , { "$date" : 1309244177602 } ] , { "$maxElement" : 1 } ] , { "$maxElement" : 1 } ] , { "$date" : 1309244177602 } ] 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)? |
| Comments |
| Comment by Githook User [ 20/Nov/13 ] |
|
Author: {u'username': u'benety', u'name': u'Benety Goh', u'email': u'benety@mongodb.com'}Message: |
| Comment by Mathieu Poumeyrol [ 14/Aug/13 ] |
|
Just got bitten — badly — by this one. |
| Comment by Eliot Horowitz (Inactive) [ 19/Apr/12 ] |
|
Yeah - we should change that |
| Comment by unbeknownst [ 18/Apr/12 ] |
|
What? so you can select fields using either booleans or numbers but the behavior of the index changes depending on the type??? If I ran: db.userevents.find({_id:ObjectId("4da41e0738710e13e4000001")}, {_id:true}) I get: { "_id" : ObjectId("4da41e0738710e13e4000001") }If I ran: db.userevents.find({_id:ObjectId("4da41e0738710e13e4000001")}, {_id:true, g:true}) I get: { "_id" : ObjectId("4da41e0738710e13e4000001"), "g" : ObjectId("4d650bb438710e13d4000001") }So obviously the field selection works with booleans, which makes sense to me, as it does on other drivers, but it does not make sense than the type of the selector influences the decision on whether to use the index only to return data or not. |
| Comment by Eliot Horowitz (Inactive) [ 18/Apr/12 ] |
|
needs to be: { _id : 1 }
|
| Comment by unbeknownst [ 18/Apr/12 ] |
|
Could you explain this case: > db.userevents.find({_id:ObjectId("4da41e0738710e13e4000001")}, {_id:true}).explain() } "indexOnly" is false and "nscannedObjects" is 1, which means is not using the index to return the data? I am using version "2.0.1". |
| Comment by Eliot Horowitz (Inactive) [ 27/May/11 ] |
|
You don't have any projection in there, so the full documents will be returned, so indexOnly is false. Notice in the 2nd case, nscanned=59 and nscannedObjects=0 which indicates it only had to use the index. |