开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜