2

I have the following query

db.runCommand(
    {"text":"item","search":"\"price\" ",
     "project":{"_id":1},
     "limit":1,
     "filter":{"quotes":{"$not":{"$size":0}}}} 
);

But the filter part is taking a long time. For your understanding, "quotes" is a simple array of embeded documents. Is it possible to create an index to find all elements having at least one quote quickly?

EDIT: To be more specific: The question is not only about "how to query" but "how to make a useful index".

KIC
  • 5,887
  • 7
  • 58
  • 98
  • Text search is really still, well almost and "alpha" phase in any current production release. There is also a **big** warning in the documentation to **not** use this in production. If you have **real** text search needs beyond the basic then use a proper text search engine. It will perform better and have more features. – Neil Lunn Mar 19 '14 at 12:20
  • possible duplicate of [In MongoDB, how do I find documents where array size is greater than 1?](http://stackoverflow.com/questions/7811163/in-mongodb-how-do-i-find-documents-where-array-size-is-greater-than-1) – Sammaye Mar 19 '14 at 12:50
  • Check the marked duplicate question here: http://stackoverflow.com/questions/7811163/in-mongodb-how-do-i-find-documents-where-array-size-is-greater-than-1 at the second answer, it provides a good way – Sammaye Mar 19 '14 at 12:51
  • yes, but my question is not about "how to query" but "how to index" – KIC Mar 19 '14 at 13:27

2 Answers2

7

I think the quicker way is this one:

db.collection.find({array: {$elemMatch: {$exists: true}}})
Giulio Pretis
  • 86
  • 1
  • 5
0

The negation operators like $not and $nin generally perform slower. Can you check if the below query performs better and meets your needs? 

db.collection.runCommand("text", {"search":"\"price\" ",
                                  "project":{"_id":1},
                                  "limit":1,
                                  "filter":{"quotes.0":{"$exists":true}}} 
);
Anand Jayabalan
  • 12,294
  • 5
  • 41
  • 52
  • no sadly not, still running 9.67 seconds which is allmost the same performance – KIC Mar 19 '14 at 12:18
  • How about you run just `db.collection.find({"quotes":{"$size":{"$gt":0}}})` just to check if it's the filter criteria that's slowing you down? Also, can you confirm if you are running this against a collection i.e., `db.collection.runCommand()` rather than `db.runCommand`? – Anand Jayabalan Mar 19 '14 at 12:26
  • You are right, just the find query is really fast and just the text search it self (without the filter) is realy fast too. hmmm .... – KIC Mar 19 '14 at 12:32
  • 1
    $size doesn't work like that, you want to test where filter.0 $exists – Sammaye Mar 19 '14 at 12:48
  • @Sammaye, thank you! You are correct, $size doesn't work. I've updated my post with your suggestion. – Anand Jayabalan Mar 19 '14 at 12:57
  • @KIC, the syntax of the text command was off. I've corrected that now along with Sammaye's suggestion. – Anand Jayabalan Mar 19 '14 at 12:58
  • exists is still consuming over 9 secs. Hmm .. here comes my question again, can I make a useful index on qoutes for this query? – KIC Mar 19 '14 at 13:26
  • If "quotes" were **not** an array, then you could have appended the field to the text index. See [this](http://docs.mongodb.org/manual/tutorial/return-text-queries-using-only-text-index/) – Anand Jayabalan Mar 19 '14 at 13:58
  • @AnandJayabalan I think that the filter is applied after the text search and not upfront. this makes makes the single find query fast and the undfiltered text serach too. Only the combination of text and filter sucks. Maybe I will do a incremental map reduce to a new collection only containing documents which have at least one quote. – KIC Mar 19 '14 at 14:16