DB/MongoDB

mongodb - $in, $all

linuxism 2014. 4. 16. 10:44



Performance

Queries that use the $all operator must scan all the documents that match the first element in the $allexpression. As a result, even with an index to support the query, the operation may be long running, particularly when the first element in the $all expression is not very selective.


출처 - http://docs.mongodb.org/manual/reference/operator/query/all/#op._S_all







MongoDB $in, $and, Implicit $and, $all

> db.users.insert({"_id":"01", joined: new Date(), "likes":["tennis", "golf", "basketball"]});
> db.users.insert({"_id":"02", joined: new Date(), "likes":["basketball", "tennis", "golf"]});
> db.users.insert({"_id":"03", joined: new Date(), "likes":["golf", "basketball", "tennis"]});
> db.users.insert({"_id":"04", joined: new Date(), "likes":["tennis", "golf"]});
> db.users.insert({"_id":"05", joined: new Date(), "likes":["golf", "tennis"]});
> db.users.insert({"_id":"06", joined: new Date(), "likes":["golf", "basketball"]});
> db.users.insert({"_id":"07", joined: new Date(), "likes":["basketball", "golf"]});
> db.users.insert({"_id":"08", joined: new Date(), "likes":["tennis", "basketball"]});
> db.users.insert({"_id":"09", joined: new Date(), "likes":["basketball","tennis"]});
> db.users.insert({"_id":"10", joined: new Date(), "likes":[]});
> db.users.insert({"_id":"11", joined: new Date(), "likes":["golf"]});
> db.users.insert({"_id":"12", joined: new Date(), "likes":["tennis"]});
> db.users.insert({"_id":"13", joined: new Date(), "likes":["basketball"]});
> db.users.insert({"_id":"14", joined: new Date(), "likes":"golf"});
> db.users.insert({"_id":"15", joined: new Date(), "likes":"tennis"});

> db.users.count()
15

> db.users.find({$and:[{likes:"tennis"}, {likes:"golf"}]})
{ "_id" : "01", "joined" : ISODate("2013-10-24T05:52:26.733Z"), "likes" : [  "tennis",  "golf",  "basketball" ] }
{ "_id" : "02", "joined" : ISODate("2013-10-24T05:52:26.795Z"), "likes" : [  "basketball",  "tennis",  "golf" ] }
{ "_id" : "03", "joined" : ISODate("2013-10-24T05:52:26.835Z"), "likes" : [  "golf",  "basketball",  "tennis" ] }
{ "_id" : "04", "joined" : ISODate("2013-10-24T05:52:26.878Z"), "likes" : [  "tennis",  "golf" ] }
{ "_id" : "05", "joined" : ISODate("2013-10-24T05:52:26.914Z"), "likes" : [  "golf",  "tennis" ] }

> db.users.find({likes:{$in:["golf", "tennis"]}})
{ "_id" : "01", "joined" : ISODate("2013-10-24T05:52:26.733Z"), "likes" : [  "tennis",  "golf",  "basketball" ] }
{ "_id" : "02", "joined" : ISODate("2013-10-24T05:52:26.795Z"), "likes" : [  "basketball",  "tennis",  "golf" ] }
{ "_id" : "03", "joined" : ISODate("2013-10-24T05:52:26.835Z"), "likes" : [  "golf",  "basketball",  "tennis" ] }
{ "_id" : "04", "joined" : ISODate("2013-10-24T05:52:26.878Z"), "likes" : [  "tennis",  "golf" ] }
{ "_id" : "05", "joined" : ISODate("2013-10-24T05:52:26.914Z"), "likes" : [  "golf",  "tennis" ] }
{ "_id" : "06", "joined" : ISODate("2013-10-24T05:52:26.945Z"), "likes" : [  "golf",  "basketball" ] }
{ "_id" : "07", "joined" : ISODate("2013-10-24T05:52:26.967Z"), "likes" : [  "basketball",  "golf" ] }
{ "_id" : "08", "joined" : ISODate("2013-10-24T05:52:27.001Z"), "likes" : [  "tennis",  "basketball" ] }
{ "_id" : "09", "joined" : ISODate("2013-10-24T05:52:27.030Z"), "likes" : [  "basketball",  "tennis" ] }
{ "_id" : "11", "joined" : ISODate("2013-10-24T05:52:27.083Z"), "likes" : [  "golf" ] }
{ "_id" : "12", "joined" : ISODate("2013-10-24T05:52:27.106Z"), "likes" : [  "tennis" ] }
{ "_id" : "14", "joined" : ISODate("2013-10-24T05:52:27.160Z"), "likes" : "golf" }
{ "_id" : "15", "joined" : ISODate("2013-10-24T05:52:27.185Z"), "likes" : "tennis" }

> db.users.find({likes:{$all:["golf", "tennis"]}})
{ "_id" : "01", "joined" : ISODate("2013-10-24T05:52:26.733Z"), "likes" : [  "tennis",  "golf",  "basketball" ] }
{ "_id" : "02", "joined" : ISODate("2013-10-24T05:52:26.795Z"), "likes" : [  "basketball",  "tennis",  "golf" ] }
{ "_id" : "03", "joined" : ISODate("2013-10-24T05:52:26.835Z"), "likes" : [  "golf",  "basketball",  "tennis" ] }
{ "_id" : "04", "joined" : ISODate("2013-10-24T05:52:26.878Z"), "likes" : [  "tennis",  "golf" ] }
{ "_id" : "05", "joined" : ISODate("2013-10-24T05:52:26.914Z"), "likes" : [  "golf",  "tennis" ] }

> db.users.find({$and:[{likes:"golf"}, {likes:"tennis"}]})
{ "_id" : "01", "joined" : ISODate("2013-10-24T05:52:26.733Z"), "likes" : [  "tennis",  "golf",  "basketball" ] }
{ "_id" : "02", "joined" : ISODate("2013-10-24T05:52:26.795Z"), "likes" : [  "basketball",  "tennis",  "golf" ] }
{ "_id" : "03", "joined" : ISODate("2013-10-24T05:52:26.835Z"), "likes" : [  "golf",  "basketball",  "tennis" ] }
{ "_id" : "04", "joined" : ISODate("2013-10-24T05:52:26.878Z"), "likes" : [  "tennis",  "golf" ] }
{ "_id" : "05", "joined" : ISODate("2013-10-24T05:52:26.914Z"), "likes" : [  "golf",  "tennis" ] }

{$and:[{key1:value1}, {key1:value2}]}가 {key1:value1, key2:value2}가 동일한기는 하지만
단, key1과 key2가 다른 키일 경우에 $and와 동일한다.
key1과 key2가 동일 키일 경우에는 key2:value2의 조건만을 비교하므로 반드시 주의!!!

> db.users.find({likes:"golf", likes:"tennis"})
{ "_id" : "01", "joined" : ISODate("2013-10-24T05:52:26.733Z"), "likes" : [  "tennis",  "golf",  "basketball" ] }
{ "_id" : "02", "joined" : ISODate("2013-10-24T05:52:26.795Z"), "likes" : [  "basketball",  "tennis",  "golf" ] }
{ "_id" : "03", "joined" : ISODate("2013-10-24T05:52:26.835Z"), "likes" : [  "golf",  "basketball",  "tennis" ] }
{ "_id" : "04", "joined" : ISODate("2013-10-24T05:52:26.878Z"), "likes" : [  "tennis",  "golf" ] }
{ "_id" : "05", "joined" : ISODate("2013-10-24T05:52:26.914Z"), "likes" : [  "golf",  "tennis" ] }
{ "_id" : "08", "joined" : ISODate("2013-10-24T05:52:27.001Z"), "likes" : [  "tennis",  "basketball" ] }
{ "_id" : "09", "joined" : ISODate("2013-10-24T05:52:27.030Z"), "likes" : [  "basketball",  "tennis" ] }
{ "_id" : "12", "joined" : ISODate("2013-10-24T05:52:27.106Z"), "likes" : [  "tennis" ] }
{ "_id" : "15", "joined" : ISODate("2013-10-24T05:52:27.185Z"), "likes" : "tennis" }

이것은 결국 아래와  동일한 결과이다.
> db.users.find({likes:{$in:["tennis]}})
> db.users.find({likes:{$all:["tennis]}})
하지만 그럴거면 아래와 같이 쓰는것이 졸겟지....
> db.users.find({likes:"tennis"})



출처 - http://wonie777.blogspot.kr/2013/10/mongodb-in-and-implicit-and-all.html



저작자표시