MongoDb Find() FullScan with Index
In mongo db
> show dbs
admin (empty)
data 23.9423828125GB
local (empty)
I have a table with an index:
> db.XXX.count();
80089670
C#:
voteCol.EnsureIndex("({ YYY:1 })");
When I am doing a query with the C# driver:
MongoCollection<BsonDocument> voteCol = database.GetCollection<BsonDocument>("XXX");
var query = new QueryDocument("YYY", DataUtils.getItemInPollIdList());
MongoCursor<BsonDocument> cursor = voteCol.Find(query).SetSortOrder(SortBy.Descending("ZZZ")).SetLimit(10).SetSkip(20);
The execution time for this part of the code is close to 0.
Then when I am trying to get the size of the cursor
cursor.Size();
I get a timeout.
Unable to read data fr开发者_如何学Pythonom the transport connection: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.
In Mongo log
Mon Aug 22 10:08:50 [conn9] query data.XXX ntoreturn:1 reslen:36 nscanned:80089670 { YYY: "1482092" } nreturned:0 48935ms
What can it be? When the query is really executed? Why I can't get the results?
Edit 1: Added new index
Mon Aug 22 10:17:38 [conn12] building new index on { ({ YYY:-1 }): 1 } for data.XXX
4000000/80089670 4% 7866400/80089670 9% 11403000/80089670 14% 15000000/80089670 18% 19000000/80089670 23% 22988600/80089670 28% 26454700/80089670 33% 30000000/80089670 37% 33438600/80089670 41% 37000000/80089670 46% 40810600/80089670 50% 44132200/80089670 55% 48000000/80089670 59% 52000000/80089670 64% 55618300/80089670 69% 59000000/80089670 73% 62170100/80089670 77% 66000000/80089670 82% 70000000/80089670 87% 74000000/80089670 92% 77874500/80089670 97%
Even with the new index - same issue: Mongo shell:
> printjson(db.XXX.findOne({YYY:"1517077"}));
MongoLog
Mon Aug 22 10:33:40 [conn4] query data.XXX ntoreturn:1 reslen:36 nscanned:80089670 { YYY: "1517077" } nreturned:0 48751ms
the size of the cursor returns its size in bytes. What you should be interested in order to prevent all scans is the number of documents. Use this snippet:
collection.FindAll().Count();
I did a mistake - all is case sensitive in mongoDb.
I added an index on yyy
instead of YYY
.
No warning message even if I have nothing named yyy in the table and the index was created successfully :(.
I recreated an index on the correct column name. Everything is fast now.
精彩评论