[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:
Depends
is depended on by JAVA-1344 Allow count to work with query hints Closed
Duplicate
is duplicated by SERVER-10749 Query results differ depending on the... Closed
Related
related to SERVER-1599 support ability to pass hint to update Closed
related to DOCS-3066 count + hint Closed
is related to SERVER-7105 Please provide a way to use $hint for... Closed
is related to SERVER-14799 Count with hint doesn't work when hin... Closed
Backwards Compatibility: Fully Compatible
Participants:

 Description   

Add ability to hint count commands.

// using explicit command form
db.runCommand({count:'test', query:{f:1}, hint:'_id_'})
 
// using shell helper
db.test.find({f:1}).hint('x_1').count()

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: SERVER-2677 Allow hint to work with the count command.
Branch: master
https://github.com/mongodb/mongo/commit/2bea8060ce966275cd1424e18157ca790af34636

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.
It is an ugly workaround API wise, but as far as what the db does it should not be bad, at least it's much better than doing a table scan or wrong index scan.
A lot of the hinting and query optimizing has been completely revamped in 2.6, so that's also something to watch for.

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.
If count is picking the wrong index, this count be many times faster.

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()
{
"cursor" : "BtreeCursor u_1",
"nscanned" : 198,
"nscannedObjects" : 198,
"n" : 85,
"millis" : 0,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" :

{ "u" : [ [ 2102, 2102 ] ] }

}

Comment by Eliot Horowitz (Inactive) [ 18/Jul/11 ]

Right now you have to use the 2d index with any $within type query.
So hinting to something else is not supported.

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()
Mon Jul 18 00:51:27 uncaught exception: count failed: {
"errmsg" : "exception: can't find special index: 2d for: { u: 2102.0, wtd: true, latlng: { $within:

{ $center: [ [ 40.7, -74.0 ], 0.1185 ] }

} }",
"code" : 13038,
"ok" : 0
}

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 ) as we do about 7 counts for every find, thus 7 out of 8 queries can't use hints right now

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