[SERVER-6176] Having compound indexes scans less objects during a query but takes more time than a single index Created: 22/Jun/12 Updated: 11/Jul/16 Resolved: 24/Jul/12 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Querying |
| Affects Version/s: | 2.0.2 |
| Fix Version/s: | None |
| Type: | Question | Priority: | Major - P3 |
| Reporter: | Justin | Assignee: | Aaron Staple |
| Resolution: | Done | Votes: | 0 |
| Labels: | performance, query | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Environment: |
MacBook Pro 2010 running Mac OS X Snow Leopard |
||
| Participants: |
| Description |
|
I am storing documents with 2 fields: "date" and "b". For "date" I want to check that it is greater than or less than a certain date. For "b" I want to know whether or not it exists. I want to query on both fields simultaneously. I created some test data as follows: date = new ISODate(); for (var i=0; i < 5000000; i++) { ); ); Then I run 2 tests. Test 1) I put an index on date only, and do a query explain. db.bar.ensureIndex( {"date":1}); Test 2) I put a compound index on date and b, and do a query explain. db.bar.ensureIndex( {"date":1,"b":1}); Results of the tests: Having a compound index on "date" and "b" runs a bit slower, even though it scans less documents. Here is the output of the explain()s: Test 1) { } Test 2) { } |
| Comments |
| Comment by Aaron Staple [ 24/Jul/12 ] |
|
No prob. |
| Comment by Justin [ 24/Jul/12 ] |
|
Using the index {b:1,date:1}worked much better. No further questions. Thanks! |
| Comment by Aaron Staple [ 23/Jul/12 ] |
|
Hi Justin, Do you have any further questions? |
| Comment by Aaron Staple [ 23/Jun/12 ] |
|
Hi Justin, A couple of points:
|