[SERVER-24077] The "hint" option does not have any effect on the index selected by db.collection.count() Created: 06/May/16  Updated: 06/May/16  Resolved: 06/May/16

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 3.2.5
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Dmitry Ryabtsev Assignee: Kelsey Schubert
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Operating System: ALL
Steps To Reproduce:

db.testcounthint.insert({a:1, b:1, c:1});
db.testcounthint.insert({a:1, b:2, c:2});
db.testcounthint.insert({a:2, b:1, c:3});
db.testcounthint.insert({a:2, b:2, c:4});
db.testcounthint.createIndex({a:1, b:1})
db.testcounthint.createIndex({b:1, a:1})
db.testcounthint.createIndex({a:1, b:1, c:1})
db.testcounthint.count({a:1, b:2}, {hint: "a_1_b_1"})

In the log:

2016-05-06T14:09:26.241+1000 D QUERY    [conn56] Relevant index 0 is kp: { a: 1.0, b: 1.0 } name: 'a_1_b_1' io: { v: 1, key: { a: 1.0, b: 1.0 }, name: "a_1_b_1", ns: "test.testcounthint" }
2016-05-06T14:09:26.241+1000 D QUERY    [conn56] Relevant index 1 is kp: { b: 1.0, a: 1.0 } name: 'b_1_a_1' io: { v: 1, key: { b: 1.0, a: 1.0 }, name: "b_1_a_1", ns: "test.testcounthint" }
2016-05-06T14:09:26.241+1000 D QUERY    [conn56] Relevant index 2 is kp: { a: 1.0, b: 1.0, c: 1.0 } name: 'a_1_b_1_c_1' io: { v: 1, key: { a: 1.0, b: 1.0, c: 1.0 }, name: "a_1_b_1_c_1", ns: "test.testcounthint" }
2016-05-06T14:09:26.241+1000 D QUERY    [conn56] Using fast count: query: { a: 1.0, b: 2.0 } sort: {} projection: {}, planSummary: COUNT_SCAN { b: 1.0, a: 1.0 }

db.testcounthint.count({a:1, b:2}, {hint: "a_1_b_1_c_1"});

In the log:

2016-05-06T14:08:42.997+1000 D QUERY    [conn56] Relevant index 0 is kp: { a: 1.0, b: 1.0 } name: 'a_1_b_1' io: { v: 1, key: { a: 1.0, b: 1.0 }, name: "a_1_b_1", ns: "test.testcounthint" }
2016-05-06T14:08:42.997+1000 D QUERY    [conn56] Relevant index 1 is kp: { b: 1.0, a: 1.0 } name: 'b_1_a_1' io: { v: 1, key: { b: 1.0, a: 1.0 }, name: "b_1_a_1", ns: "test.testcounthint" }
2016-05-06T14:08:42.997+1000 D QUERY    [conn56] Relevant index 2 is kp: { a: 1.0, b: 1.0, c: 1.0 } name: 'a_1_b_1_c_1' io: { v: 1, key: { a: 1.0, b: 1.0, c: 1.0 }, name: "a_1_b_1_c_1", ns: "test.testcounthint" }
2016-05-06T14:08:42.997+1000 D QUERY    [conn56] Using fast count: query: { a: 1.0, b: 2.0 } sort: {} projection: {}, planSummary: COUNT_SCAN { b: 1.0, a: 1.0 }

Participants:

 Comments   
Comment by Kelsey Schubert [ 06/May/16 ]

Hi dmitry.ryabtsev,

Thanks for the additional information about the mongo shell version. Running your reproduction steps with mongo shell 3.2.5 generates the correct query plans.

Since MongoDB 3.0 does not support the inclusion of options in the count shell wrapper, db.collection.count(), the behavior you describe is expected.

Best regards,
Thomas

Comment by Dmitry Ryabtsev [ 06/May/16 ]

Hi anonymous.user, sorry I just realized I've been using mongo shell 3.0.6 with mongod 3.2.

Generated at Thu Feb 08 04:05:19 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.