Search code examples
mongodbindexing

MongoDB unique index vs compound index


I have a question during the index generation process. In the following situation, is it okay to create only the compound index? What are the benefits of creating a unique index separately?

  • When the following 'compound index' and 'unique index' are applied, the 'unique index' is used when querying the condition of the 'memberId' and 'where' clause, and when only the 'compound index' is applied, the 'compound index' is used.
  • Then, is it okay to configure only the 'compound index' without creating the 'unique index'?
//compound index
  memberId_1_contentId_1
//unique index
  memberId_1

//example query
default List<ContentBuyList> getContentBuyListByMemberId(String memberId) {
return this.findAll(
contentBuyList.memberId.eq(memberId)
);
}

Solution

  • This is an excellent question, and not as easy to answer as you might first think.

    I think that if you look at usage purposes, it is really enough to just create a 'compound index', since its basically a 'group by' based on the first member and 'sort' based on the additional member.

    So the answer is yes, just the 'compound index' is sufficient. But because the database searches in the compound index it is a very slightly slower search than if you just have a 'unique index'. This difference is minimal and only comes out if the number of elements in the collection is large.

    However, since the database only has to maintain one index, the write is slightly faster and it doesn't need to run a uniqueness check.

    So if the formatting is hard and infrequent writing, and there are a lot of elements, then it is not worth creating just the 'compound index'. But if the application is writing heavy and there are few elements, then you don't need a 'unique index'