[SERVER-18119] Covered query over compound index using the second field does not results in an index-only query Created: 20/Apr/15  Updated: 20/Apr/15  Resolved: 20/Apr/15

Status: Closed
Project: Core Server
Component/s: Index Maintenance, Performance, Querying
Affects Version/s: 2.6.8
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Daniel Breitlauch Assignee: Unassigned
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Operating System: ALL
Steps To Reproduce:

Have a collection with this index:

{
    "a" : 1,
    "b" : -1,
    "c" : 1
}

query this:

{ b : { $lte : 1262343601554}}, {b : 1, _id : 0}

will not even use the index… so we hint the index. and limit to 10.
An explain tells now:

{
    "cursor" : "BtreeCursor indexName",
    "isMultiKey" : false,
    "n" : 10,
    "nscannedObjects" : 72,
    "nscanned" : 72,
    "nscannedObjectsAllPlans" : 72,
    "nscannedAllPlans" : 72,
    "scanAndOrder" : false,
    "indexOnly" : false,

Participants:

 Description   

We have a non sharded collection with a compound index.
On a query using only the second field of the compound index and restricting the returned fields to those of the index the query will not be a covered query.

But even as it has to go through more elements of the index it should only use the index.



 Comments   
Comment by Daniel Breitlauch [ 20/Apr/15 ]

The point is with a hint it uses the index but does not make it covered.
So the index is used to get the documents.
The problem is that it additionally goes to the collection to get the documents even then when the fields are in the index.

Comment by Daniel Pasette (Inactive) [ 20/Apr/15 ]

You must include the prefix of a compound index in your query to enable its use in a query. Check the documentation and explanation of how compound indexes work here for more info:

http://docs.mongodb.org/manual/core/index-compound/#prefixes

Generated at Thu Feb 08 03:46:38 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.