Info

I haven't spotted any indexing errors, but today I noticed a very odd behavior: > db.saved_search.find({'organization': DBRef('organization', 'orga_qr7OtHV26J9tr3TJ3aEhDHLccsulXbD9sJI4wnkhKw8'), 'user': DBRef('user', 'user_UlODAoCGCqnOjWCv0dXIPTnz39GQWOT4DD3rjRLcECD')}).count() 2 > db.saved_search.find({'organization': DBRef('organization', 'orga_qr7OtHV26J9tr3TJ3aEhDHLccsulXbD9sJI4wnkhKw8'), 'user': DBRef('user', 'user_UlODAoCGCqnOjWCv0dXIPTnz39GQWOT4DD3rjRLcECD')}).hint({ _id: 1 }).count() 2 > db.saved_search.find({'organization': DBRef('organization', 'orga_qr7OtHV26J9tr3TJ3aEhDHLccsulXbD9sJI4wnkhKw8'), 'user': DBRef('user', 'user_UlODAoCGCqnOjWCv0dXIPTnz39GQWOT4DD3rjRLcECD')}).toArray().length 2 > db.saved_search.find({'organization': DBRef('organization', 'orga_qr7OtHV26J9tr3TJ3aEhDHLccsulXbD9sJI4wnkhKw8'), 'user': DBRef('user', 'user_UlODAoCGCqnOjWCv0dXIPTnz39GQWOT4DD3rjRLcECD')}).hint({ _id: 1 }).toArray().length 14 There are two issues I see here: 1. (minor) count doesn't respect the hint (had to resort to toArray().length) 2. (major) the default index utilized for this query is broken (doesn't return all the docs that match the query). A bit more insight into the data: Indexes on the collection: > db.saved_search.getIndexes() [ { "v" : 1, "key" : { "_id" : 1 }, "ns" : "closeio.saved_search", "name" : "_id_" }, { "v" : 1, "key" : { "organization" : 1, "user" : 1, "query" : 1, "is_shared" : 1, "date_created" : -1 }, "ns" : "closeio.saved_search", "name" : "organization_1_user_1_query_1_is_shared_1_date_created_-1", "background" : false, "dropDups" : false } ] One of the returned docs: { "_id" : "save_m0n9GBfilJ3rovhDZVET9bPyM1nMu0a31wtWaofJtDy", "date_created" : ISODate("2013-08-22T07:18:11.583Z"), "date_updated" : ISODate("2013-09-03T14:25:18.482Z"), "is_shared" : false, "name" : "Synchs streak", "organization" : DBRef("organization", "orga_qr7OtHV26J9tr3TJ3aEhDHLccsulXbD9sJI4wnkhKw8"), "query" : "custom.Stage:Sync/Implemented not opportunity_status:\"Synch/ Implemented\"", "user" : DBRef("user", "user_UlODAoCGCqnOjWCv0dXIPTnz39GQWOT4DD3rjRLcECD") } One of the docs that weren't returned (but should've been): { "_id" : "save_OgHsF7KrU8kD5J1SoTU7jLtutAZQNNIZJ7NmDOmW9NK", "is_shared" : false, "date_updated" : ISODate("2013-09-11T14:20:09.108Z"), "query" : "country:uk and description:ebay not ( \"custom.Lead Owner\":\"Bagdat Baimagambetov\" \"custom.Lead Owner\":\"Brian OLeary\" \"custom.Lead Owner\":\"Chris Marley\" \"custom.Lead Owner\":\"Duncan Anderson\" \"custom.Lead Owner\":\"Gina Karlikoff\" \"custom.Lead Owner\":\"Jim Gregory\" \"custom.Lead Owner\":\"John Nuclear\" \"custom.Lead Owner\":\"Liam Delahunty\" \"custom.Lead Owner\":\"Louis McCarthy\" \"custom.Lead Owner\":\"Matteo Pedrioli\" \"custom.Lead Owner\":\"Patrice Meiner\" \"custom.Lead Owner\":\"Rory Kierans\" \"custom.Lead Owner\":\"Rory Reilly\" \"custom.Lead Owner\":\"Sujeet Gorahava\" \"custom.Lead Owner\":\"trial - jim\" \"custom.Lead Owner\":Trial-Dave \"custom.Lead Owner\":trial-joana \"custom.Lead Owner\":trial-matthew \"custom.Lead Owner\":triál-dave ) not lead_status:\"No Contact Details\" not lead_status:\"Won and then Lost\" not opportunity_status:\"eBay Won\" not opportunity_status:\"Won Web Shop\"", "user" : DBRef("user", "user_UlODAoCGCqnOjWCv0dXIPTnz39GQWOT4DD3rjRLcECD"), "date_created" : ISODate("2013-09-11T14:20:09.108Z"), "organization" : DBRef("organization", "orga_qr7OtHV26J9tr3TJ3aEhDHLccsulXbD9sJI4wnkhKw8"), "name" : "UK leads" }

Top User Comments

dan@10gen.com commented on Mon, 16 Sep 2013 08:05:20 +0000: The major problem here is that the index key is too long to be indexed for the document which is not returned and thus it is not in the index at all. When you hint to use the _id index, it forces a table scan and is able to find all the documents. See: SERVER-5290 and SERVER-3372. When it encounters a long key mongod will log an error message something like: Mon Sep 16 03:32:05.949 [conn1] dbref.system.indexes Btree::insert: key too large to index, skipping dbref.a.$organization_1_user_1_query_1_is_shared_1_date_created_-1 1054 { : { $ref: "organization", $id: "orga_qr7OtHV26J9tr3TJ3aEhDHLccsulXbD9sJI4wnkhKw8" }, : { $ref: "user", $id: "user_UlODAoCGCqnOjWCv0dXIPTnz39GQWOT4DD3rjRLcECD" }, : "country:uk and description:ebay not ( "custom.Lead Owner":"Bagdat Baimagambetov" "custom.Lead Owner":"Brian OLeary" "custom.Lead Owner":"Chris Marley"...", : false, : new Date(1378909209108) } Mon Sep 16 03:32:05.949 [conn1] warning: not all entries were added to the index, probably some keys were too large The minor problem you found is due to the fact that count() is a command, not an operation on the cursor. Commands do not support hint(), so the command is using the index with missing entries. See SERVER-2677.

Steps to Reproduce