[SERVER-2677] Allow count to work with query hints Created: 04/Mar/11 Updated: 27/Oct/15 Resolved: 16/Jan/14 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | None |
| Affects Version/s: | 1.8.0-rc0 |
| Fix Version/s: | 2.5.5 |
| Type: | Improvement | Priority: | Minor - P4 |
| Reporter: | Yunyang Zhang | Assignee: | Daniel Pasette (Inactive) |
| Resolution: | Done | Votes: | 20 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Environment: |
Redhat 6.0 |
||
| Issue Links: |
|
||||||||||||||||||||||||||||||||||||
| Backwards Compatibility: | Fully Compatible | ||||||||||||||||||||||||||||||||||||
| Participants: | |||||||||||||||||||||||||||||||||||||
| Description |
|
Add ability to hint count commands.
If there is no predicate for the query given to the count command, the hint will be ignored and the result will be pulled directly from the collection metadata. |
| Comments |
| Comment by Githook User [ 16/Jan/14 ] |
|
Author: {u'username': u'monkey101', u'name': u'Dan Pasette', u'email': u'dan@10gen.com'}Message: |
| Comment by Jake [ 14/Jan/14 ] |
|
Not entirely sure that's accurate - we had very fast .count() before we changed our indexes, likewise a fast .explain(), but now .count() is super slow (90 seconds or more), and .explain() with a hint is still 17 seconds. We added a compound index to actually match the queries we're doing more closely - dropping one that isn't used, yet it slowed down. Not entirely sure what's going on there. We've had to resort to not counting and just doing a boolean check (for pagination). |
| Comment by Antoine Girbal [ 14/Jan/14 ] |
|
In itself, explain does not do that much more work than a count(), for example it will properly do covered only index if it is possible. |
| Comment by Jake [ 14/Jan/14 ] |
|
This is nuts. Just ran into this today. Leveraging compound indexes great, except when the query optimizer totally ignores your compound index in favor of less optimized indexes. Count doesn't support $hint or .hint()? This is killing one of our applications. .explain() is an absolutely terrible work-around. Any chance we can get priority bumped on this? |
| Comment by Christopher Price [ 01/Nov/13 ] |
|
Additional note: in most of my examples where mongodb has chosen a less than ideal index, the mongo team has recommended using $hint. So support for $hint on count operations is the only way for us to follow your own advice. |
| Comment by Christopher Price [ 01/Nov/13 ] |
|
I have had several problems where mongo picks a less than ideal index for find and count operations. I proposed using explain on all of my operations to prevent bad query plans from being cached and it was strongly discouraged by the mongo team. I don't think that using explain on application queries in a production system is a very good (nor scalable) option. |
| Comment by Antoine Girbal [ 31/Oct/13 ] |
|
A current workaround would be to actually use "n" from find().hint().explain() instead of doing the count. |
| Comment by Eliot Horowitz (Inactive) [ 18/Jul/11 ] |
|
@kushal, those results are not going to be correct. |
| Comment by Kushal Dave [ 18/Jul/11 ] |
|
Hinting seems to work for fetching results > db.usertodos.find({'u':2102,'wtd':true,'latlng':{'$within':{$center:[[40.7, -74], 0.1185]}}}).hint( {u:1}).explain() } |
| Comment by Eliot Horowitz (Inactive) [ 18/Jul/11 ] |
|
Right now you have to use the 2d index with any $within type query. That would definitely be a separate issue. |
| Comment by Kushal Dave [ 18/Jul/11 ] |
|
One case we've run into is that Mongo insists on using a 2d index if there's any geo restriction in the query (should this be a separate bug?). As a result, if we don't have or shouldn't use the 2d index, we need to hint our way out of this. See below. I suppose I can work around by doing the lat lng query as less than, greater than, but it makes me feel yucky to do so. > db.usertodos.find({'u':2102,'wtd':true,'latlng':{'$within':{$center:[[40.7, -74], 0.1185]}}}).hint( {u:1}).count() } }", |
| Comment by Eliot Horowitz (Inactive) [ 17/Jul/11 ] |
|
@boris - what's the case where you need hint? We see that very rarely. |
| Comment by borisman [ 14/Jul/11 ] |
|
Guys, this bug renders count() unusable for most practical reasons. It NEEDS to be fixed. |
| Comment by Thomas DeMille [ 22/Jun/11 ] |
|
I know this issue only has two votes but this is somewhat critical (at least for me |