Here is the issue. We are working with MongoDB-PHP.
In our application, we have many user groups where users can make posts. Presently we are maintaining the post ids these groups in the document of that group in array format. So that, when we need to grab first 10 posts we can grab them from the array using slice operation.
Eg: Case 1
collection posts: //this collection stores all the posts of various groups
{
{"_id":"1","post_text":"....",...}
{"_id":"2","post_text":"....",...}
} `
collection groups: //this collection contains documents for each group
{
{
"_id":"1"
"name":"Group ABC",
"post_ids":{"1","2"...."100"}
//1,2..100 represents MongoIDs of corresponding posts of this group
//so i can slice first 10 posts of this group when someone visits this page
}
}
`
In contrast to storing these post ids in document of the group, if we use indexing on group id and store that in posts collection.
Eg: Case 2
collection posts
{
{"_id":"1","group_id":"1","post_text":"....",...}
{"_id":"2","group_id":"2","post_text":"....",...}
}
Also note that in Case 1 we do not have to apply any sorting operations as array elements are pushed in order while in Case 2 we will have to apply sort(by timestamp criteria) after the find operation, which would read all documents from memory and then apply sorting on them.
Whose performance would be better taking into consideration that indexes would be stored in RAM ?
Please let me know if the issue is not clear from this question.
