[SERVER-35218] different result when query hint hint different indexes Created: 25/May/18  Updated: 27/Oct/23  Resolved: 31/May/18

Status: Closed
Project: Core Server
Component/s: Index Maintenance
Affects Version/s: 3.0.12, 3.2.16, 4.0.0-rc0
Fix Version/s: None

Type: Question Priority: Major - P3
Reporter: Jiangcheng Wu Assignee: William Byrne III
Resolution: Works as Designed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: File sample.tgz    
Issue Links:
Related
related to DOCS-11661 Add a warning to discourage people fr... Closed
related to SERVER-5290 fail to insert docs with fields too l... Closed
Participants:

 Description   

//collection indexes
> db.Collection.getIndexes()> db.Collection.getIndexes()
[ 
... 
{ "v" : 2, "unique" : true, "key" : { "user" : 1, "word" : 1 }, "name" : "user_1_word_1", "ns" : "sample.Collection", "background" : true }, 
{ "v" : 2, "key" : { "user.$id" : 1, "word" : 1, "image" : 1 }, "name" : "user.$id_1_word_1_image_1", "ns" : "sample.Collection", "background" : true }]

query results with different results

// storeage Engine: wiredTiger
> db.Collection.find({"user" : DBRef("_User", ObjectId("5a5d783bee920a005896c55f"))},{"_id":1,"user":1}).hint("user_1_word_1").count()
39
> db.Collection.find({"user" : DBRef("_User", ObjectId("5a5d783bee920a005896c55f"))},{"_id":1,"user":1}).hint("user.$id_1_word_1_image_1").count()
8

index form will affect the data? Or were there right?

I reproduces on v3.0, v3.2, v4.0-rc, may be affect more. reproduce process:

// download attachments to $data_full_path
// md5: a14368dbab317182a4826caa0e7bc41d  sample.tgz
// pull mongo images
docker pull mongo:3.4.0-rc
docker run -v $data_full_path:/x mongo:4.0-rc  --setParameter failIndexKeyTooLong=false
// another terminal in docker
mongorestore --db sample --dir=./sample
// then query
mongo 127.0.0.1:27017/sample --eval 'db.Collection.find({"user" : DBRef("_User", ObjectId("5a5d783bee920a005896c55f"))},{"_id":1,"user":1}).hint("user_1_word_1").count()'
// result: 39
mongo 127.0.0.1:27017/sample --eval 'db.Collection.find({"user" : DBRef("_User", ObjectId("5a5d783bee920a005896c55f"))},{"_id":1,"user":1}).hint("user.$id_1_word_1_image_1").count()'
// result: 8

Any help will be appreciate.



 Comments   
Comment by William Byrne III [ 30/May/18 ]

Hello Jiangcheng,

This issue of the same query returning fewer documents depending upon which index is used is caused by starting MongoDB with the failIndexKeyTooLong set to false, which permits indexes to be created that do not link to all documents in a collection.

MongoDB has always had a limit on the total size of an index entry of 1024 bytes. In releases up to version 2.4, if a document had key fields for an index that exceeded this limit, then that document was left out of the index.

Version 2.6 implemented a stricter enforcement of the limit, and documents with overly long key field values would either fail to insert if the index already existed, or the createIndex command would fail if such a document was already in the collection.

To assist customers upgrading from version 2.4 to 2.6 who had indexes and documents that violated the limit, the failIndexKeyTooLong parameter was added so they could disable the strict enforcement while they cleaned up their data or changed their indexing strategy. This parameter should not be enabled permanently, or for any purpose other that this upgrading use case.

I recommend that you drop the "user.$id_1_word_1_image_1" index and consider replacing it with a  hashed index  on the image field field alone.

Regards,

William Byrne III

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