[SERVER-4510] sort with index hint throws error unless limit applied Created: 16/Dec/11  Updated: 07/Mar/14  Resolved: 17/Dec/11

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

Type: Bug Priority: Major - P3
Reporter: Robert Gibbon Assignee: Aaron Staple
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

RHEL 5.7 x86_64


Operating System: ALL
Participants:

 Description   

db.subscriber.ensureIndex({
mnoId : 1,
status: 1,
suspended : 1,
crmEvents : 1
},
{ name : "ix_cvr_invite_1"
})

WORKS:

db.subscriber.find({
"mnoId" : "4ee0c5e898452dd133f84e9d",
"status" : "TO_INVITE" ,
"suspended" : false ,
"$and" : [ { "profiles" : { "$elemMatch" :

{ "name" : "Language" , "values" : "English"}

}}],
}).sort({
"crmEvents.0.timestamp" : 1
}).hint("ix_cvr_invite_1").limit(30000);

BROKEN:

db.subscriber.find({
"mnoId" : "4ee0c5e898452dd133f84e9d",
"status" : "TO_INVITE" ,
"suspended" : false ,
"$and" : [ { "profiles" : { "$elemMatch" :

{ "name" : "Language" , "values" : "English"}

}}],
}).sort({
"crmEvents.0.timestamp" : 1
}).hint("ix_cvr_invite_1");



 Comments   
Comment by Robert Gibbon [ 17/Dec/11 ]

Ok got it working without error by changing the index to:

db.subscriber.ensureIndex(

{ mnoId : 1, status: 1, suspended : 1, "crmEvents.0.timestamp" : 1 }, {name : "ix_cvr_invite_1"}

);

Thank you! You can close the ticket.
R

Comment by Robert Gibbon [ 17/Dec/11 ]

Yes, this is the error that's thrown. Any ideas how to resolve this?
tx

Comment by Aaron Staple [ 17/Dec/11 ]

The index you are hinting is not ordered by the sort order you specified. Is your error message something like "too much data for sort() with no index"?

Comment by Eliot Horowitz (Inactive) [ 17/Dec/11 ]

What error is thrown?

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