slow $in query

[tenorviol]

I'm encountering a slow query problem that I don't understand. I have
a collection with the _id equal to user ids. This I need to query for
specific ids. Example:

ids = [2, 24, 1027, 28974987, 287874873897, ... ];
db.mycoll.find({_id:{$in:ids}});

If the number of ids is relatively low (i.e. 100 or less), the query
runs pretty fast. If the number of ids is large (i.e. 1700), this
query slows down greatly. We set the cursor timeout to 2000ms (2s) for
all web requests. Often the 1700 ids query will timeout--sometimes
during cursor initialization other times during a next op, but if I
divide that into 17 x 100 id queries they all finish well before the
2s. Why would this make such a drastic difference to the query
optimizer?

mongodb 1.6.3 on a relatively gimpy dev box alongside nginx, php-fpm,
mysql, memcache and kestrel.

[roger]

we're looking at this, but 2 quick questions:
- what driver are you using
- can you put an .explain() on both the small and large query ?

We fixed a similar (but different) issue in the java driver, the db
was fine.

-Roger

[Andreas Svensson]

Can just add in that I just did a test myself, 2 million documents in
a collection (randomly generated and distributed), doing a partially
indexed query (14000 results) and going through all the results takes
about 600ms, doing an $in query on those 14000 ids takes 300ms. Since
the initial query isn't entirely indexed this isn't really a fair
comparison, but from what I can gather I have no such problems with
MongoDB 1.6.4 and PHP-driver 1.1.0.

However, something I did notice was that when making the first query
entirely indexed (120 000 results), it took about 1700ms to do the
indexed query, while doing the $in query took about 7000ms... which is
bad... however, I do believe there is a really simple reason for that,
which probably should be fixed... while doing the $in query, the
mongod-console outputs the entire query (meaning, approx ~50 000 lines
of objectids), which is SLOW. And I can't find a way to prevent it
from doing that (--quiet doesn't do it). Trimming down query output
longer than a few lines is probably a good idea.

Also, just adding in that while trying to CTRL-C out of the $in query,
the query continued outputting the query string, but PHP immediately
sent the following PHP-error.

"Fatal error: Uncaught exception 'MongoCursorException' with message
'bad response length: 19, max: 67108864, did the db assert?' in
XXX.php:YYY Stack trace: #0 XXX.php(YYY): MongoCursor->next() #1
{main} thrown in XXX.php on line YYY"

It may even be by design, but now you know, just in-case.

[tenorviol]

- what driver are you using

PHP 1.1.0

- can you put an .explain() on both the small and large query ?

Have to get back to you on this.

Also, I realized this bug was documented for the Windoze version a
while back:

https://jira.mongodb.org/browse/PHP-29