[SERVER-2148] $in with sort doesnt use index Created: 25/Nov/10  Updated: 07/Mar/14  Resolved: 14/Dec/11

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

Type: Improvement Priority: Minor - P4
Reporter: ppalka Assignee: Unassigned
Resolution: Duplicate Votes: 11
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

I`m not sure it not similar like http://jira.mongodb.org/browse/SERVER-1205


Issue Links:
Duplicate
duplicates SERVER-3310 Query optimizer should efficiently ha... Closed
Participants:

 Description   

Query

I have index on
db.ogloszenia2.ensureIndex(

{id:1, x:-1}

);

db.ogloszenia2.find({"id":397).limit(200).sort({"x":-1); //use index
db.ogloszenia2.find({"id":422).limit(200).sort({"x":-1); //use index
db.ogloszenia2.find({"id":{$in:[397,422]}).limit(200); //use index

db.ogloszenia2.find({"id":{$in:[397,422]}).limit(200).sort({"x":-1); //does not use index



 Comments   
Comment by Glen Xiao [ 11/May/12 ]

+1 vote. In this case, it's better to run "sort" on index than full table scan. Or I have to create an index on sort field and pray for good luck.

Comment by Ryan Ahearn [ 30/Dec/10 ]

I think this is a bug.

As you can see from the output at http://pastie.org/1417475 the first query, which includes two $in fields, does use the index, while the last query, which has one $in clause cannot use the index even though it is specified with hint(). The query optimizer should handle which case would be slower when using the index, so specifically stating which index should be used should work.

Comment by Eliot Horowitz (Inactive) [ 26/Nov/10 ]

This is less a bug and more a lack of a feature.

To use the index you'd have to read multiple ranges and merge.

IN some cases, that might actually be slower, so have to be careful.

Generated at Thu Feb 08 02:59:07 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.