[SERVER-17024] 2dsphere index doesn't work when you put it as third field in ensureIndex() Created: 23/Jan/15 Updated: 09/Feb/15 Resolved: 09/Feb/15 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Index Maintenance |
| Affects Version/s: | 2.6.7 |
| Fix Version/s: | None |
| Type: | Bug | Priority: | Minor - P4 |
| Reporter: | airs0urce | Assignee: | J Rassi |
| Resolution: | Done | Votes: | 0 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Operating System: | ALL | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Steps To Reproduce: |
Query time - 2 milliseconds. That's good.
Query time - 1410 milliseconds. Also nscanned=383926 (I have 49640 documents in users collection) |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Participants: | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Description |
|
I have "users" collection with these fields: I want index for all the fields. http://docs.mongodb.org/manual/tutorial/build-a-2dsphere-index/ So, it means I can create indexes different ways Works in queries:
Works in queries:
In this case index will not be used in queries
Not sure if this is bug. But if not then it makes sense to change docs and inform |
| Comments |
| Comment by J Rassi [ 09/Feb/15 ] |
|
Hi, This is expected behavior. The order of fields in an index definition dictates exactly what kinds of queries can be performed efficiently using that index. For your example query, two of the three indexes generate efficient plans, and one of the three indexes does not generate an efficient plan. To give an example, suppose I wanted to create an index to efficiently support the query {firstName: "John", lastName: {$gte: "N"}} over a collection of user documents. The index {firstName: 1, lastName: 1} can efficiently answer this query, as the query will scan at most the set of documents where firstName is "John" (presumably a small set). The index {lastName: 1, firstName: 1} won't efficiently answer this query, as the query will scan at most the set of documents where lastName starts with one of the letters N-Z (presumably a large set). When creating a compound index to support a particular query, the fields corresponding to the most selective predicates should come first in the index definition. My suspicion as to why you're seeing poor query performance is that the predicate on "l" is your most selective of your three predicates. See the following blog post for more information on optimizing MongoDB queries: <http://emptysqua.re/blog/optimizing-mongodb-compound-indexes/>. Please post on the mongodb-user mailing list for future questions about how to optimize queries: <https://groups.google.com/forum/#!forum/mongodb-user>. ~ Jason Rassi |
| Comment by airs0urce [ 04/Feb/15 ] |
|
I also could share users collection as now there is only test data (500,000 users now) |