Index design Question

[kb]

Hi,
I have a question on designing index.

Say i have a users collection and groups collection.

user {
name : "" ,
age : 19
}

group {
name : ""
members : [],
posts : [ { date : "" , author : "" , topic : "" }, { date : "" ,
author : "" , topic : "" } ......]
}

There can be 1000's of groups and each group can have millions of
posts.
I operation i frequently perform is a) getting posts based on date
(70%) b) updating posts (30%)
So, essentially i need to index on date.

My question is
1. Should i create a new posts collection like
posts {
name : "", date : "" , author : "" , topic : ""
} and create a single-value index on date
( db.posts.ensureIndex({posts : 1}) )

OR
should i include posts inside of group object and create an embedded
index like db.groups.ensureIndex({ posts.date : 1})

Which one is more efficient ? whats the best practice if this needs to
scale to millions of posts ?

Thanks

[Wes Freeman]

"millions" of items in a subdocument is not a good idea. There is
actually a size limit to the document, and imagine dealing with the
enormous objects you'd have to send back and forth. You probably need
to go with a posts collection.

Hope that helps.

Wes

> --
> You received this message because you are subscribed to the Google Groups "mongodb-user" group.
> To post to this group, send email to mongod...@googlegroups.com.
> To unsubscribe from this group, send email to mongodb-user...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/mongodb-user?hl=en.
>

[kb]

Wes,

Thanks. OK that limit was may be too much. Say if there were 1000's of
posts belonging to each group,
Is still creating new posts collection recommended ?
I personally felt creating a new posts collection is clean ( may be
old school rdbms ),
but since MongoDb best practices say, embed as much data in same
collections, thought of experimenting with it

2. Does index performance matter if it is an embedded index or non-
embedded one ?

[Wes Freeman]

The index structure is the same for embedded vs non-embedded. I'll
defer to the experts about whether there are minor performance
differences, but I doubt it. There's also the matter of dealing with
the document once you query it. With your original design, you would
get all the posts at once, and have to deal with sorting/filtering
them out on the client. There's no way to get partial arrays back in a
query, yet.

I don't think that best practice applies the way you're trying to make
it apply. However, say you have a posts collection, and you want to
display the author, and some info about the author, you might consider
storing that extra info in the post, so that you don't have to do two
queries to list posts (one for the posts and one for the authors).
This has the downside of not updating what's displayed on older posts
(automatically, at least) when the author updates their info, but
sometimes that's desired anyway.

Wes

[kb]

OK.. just got confused by ur first answer. ( put up another
question :) )
Do you mean, with first approach

group {
name : ""
members : [],
posts : [ { date : "" , author : "" , topic : "" }, { date : "" ,
author : "" , topic : "" } ......]
}

can i get a array of posts between two date ranges ? (and also index
on posts.date to make it faster ? )
I could not find any query helpers to achieve getting a range of
values within an array based on a field in sub document

[Wes Freeman]

There is a way to get partial arrays with slice, but that only works
on position, not a field filter, as far as I know.

Wes

[kb]

Yes, that's what i too understand from this -
http://www.mongodb.org/display/DOCS/Retrieving+a+Subset+of+Fields#RetrievingaSubsetofFields-RetrievingaSubrangeofArrayElements
and after some thinking and research, this's what i found out.

1. First, there's a limit on size of document ( currently 16MB ), and
as post schema/posts increase in size, this may stop scaling some day
as number of posts increase. and u cannot add an index to search
within array of sub documents, as indexes are only across collections.

2. Second, If posts are stored as embedded sub documents, there would
be no way to search within posts for a group in a date range. I have
to get entire posts array and do processing on client side which is
inefficient. There's no way to compare array objects based on a field
in sub document as of now.

Hence better way is to create a separate posts collection , and have
foll data

posts{
group_name : objectID(<ID in groups collection>),

date : "",
author : "",
topic : ""
}

By this way, i can as well create index on date and get all data for a
group in a date range more effectively.
I'm going ahead with this

Thanks

[kb]

Curious to know on unanswered items .

1. Does index performance matter if it is an embedded index or non-
embedded one ?

2. If this is a collection

group {
name : ""
members : [],
posts : [ { date : "" , author : "" , topic : "" }, { date : "" ,
author : "" , topic : "" } ......]
}

How to get posts of a particular group name within specific dates ?
( i.e. B/n a start date and end date for a given group)



On Jan 27, 11:58 pm, kb <keshavabharad...@gmail.com> wrote:
> Yes, that's what i too understand from this -http://www.mongodb.org/display/DOCS/Retrieving+a+Subset+of+Fields#Ret...

[Scott Hernandez]

On Fri, Jan 27, 2012 at 2:21 PM, kb <keshavab...@gmail.com> wrote:
> Curious to know on unanswered items .
>
> 1. Does index performance matter if it is an embedded index or non-
> embedded one ?

No, the index is the same; if I understand what you mean.

> 2.  If this is a collection
> group {
> name : ""
> members : [],
> posts :  [ { date : "" , author : "" , topic : "" }, { date : "" ,
> author : "" , topic : "" } ......]
> }
>
> How to get posts of a particular group name within specific dates ?
> ( i.e. B/n a start date and end date for a given group)

find({"group.name":"", "group.posts.date":{$gt:value, $lt:value}})

[kb]

Scott,

The requirement is to get a subarray of posts within a document. I
this case to get
a array of posts , which are within a date range in a "SINGLE
DOCUMENT" and not across documents.
i.e. given a group name, get a sub array of posts.

What u have mentioned is across documents and within a collection.

On Jan 28, 1:43 am, Scott Hernandez <scotthernan...@gmail.com> wrote:

[kb]

ny pointers to do that ?

[Scott Hernandez]

All indexes are on fields in a document and point to the document, not
embedded elements within the document. The indexe is for all documents
in a collection and helps by narrowing down which document to return
given a query criteria.