The $in operator in _find makes the queries very slow

[kobeyk]

Expected Behavior

First of all, I am a programmer from China. Here are my questions:

{ "limit": 20, "skip": 0, "fields": [ "_id", "fid", "_rev", "name", "des", "type", "fname", "extension", "mtime", "uid", "icon" ], "selector": { "$and": [ { "fid": { "$in": [ 3692398112768, 3701629775872, 3727986782208, 3781082476544, 3805799510016, 3841321070592, 3849785176064, 3695615143936, 3698387578880, 3721196204032, 3777047556096, 3785226448896, 3813181485056, 3831992938496, 3853979480064 ] } } ] }, "execution_stats": true }

The index has been established,But when the query, it doesn't seem to work?

"execution_stats": { "total_keys_examined": 0, "total_docs_examined": 2935, "total_quorum_docs_examined": 0, "results_returned": 15, "execution_time_ms": 9942.517 }

A total of 15 records, using nearly 10 seconds,Did I do something wrong?

[wohali]

@garrensmith correct me if I'm wrong, but doesn't the $in operator always requires a full database scan?

@kobeyk did you create an index first? If so, what is the output of the same query being sent to the /{db}/_explain endpoint instead?

[kobeyk]

@wohali this is my query explain:

this is my query explain

full database scan

[garrensmith]

That is weird. It is using an index ddoc": "_design/b25ce9a6eaeca74ab29f8b0b4d67f693362264", But not sure why its taking so long. If you run other queries that are just simple $eq queries are they also that slow. If you run the above query multiple times is it still that slow. Is the index fully built?

[wohali]

@garrensmith I thought that $in operations always ended up in a full scan, regardless of index, am I wrong?

[garrensmith]

@wohali we recently changed that. In some cases, it can use an index. The explain in the link above gives some nice insight. Mango is using the index and doing a full index scan and then doing an in memory comparison, comparing each doc against the $in selector.

[willholley]

It should be possible to pass keys to the underlying view in some cases. Perhaps this isn't ideal, but I would think is preferable to a range scan and in-memory comparison.

[risabhsharma71]

@garrensmith $in queries are really slow,my data set is of a million records.It takes around 2 mins to execute. for me $eq is fast.even after running the $in query multiple times it slow.

[ChiragMoradiya]

We also encountered this issue. Any plan to be fixed in near future? Alternatively, we are building query using $or operator which properly uses index.