Compound Index Vs Multiple Single Field Index in MongoDB 3.x
Tags: mongodb32, mongodb, mongodb34
Published: 2017-04-08 12:52:25

---
title: Compound Index Vs Multiple Single Field Index in MongoDB 3.x
published: true
description: Investigating performance impact between compound and single field index
cover_image: 
tags: mongodb3.2, mongodb, mongodb3.4, 
---

<h1>Compound Index Vs Multiple Single Field Index in MongoDB 3.x</h1>
MongoDB supports both <a href="https://docs.mongodb.com/manual/indexes/">compound index and single field index</a>. It's a common knowledge that index decreaseÂ your INSERTS/UPDATESÂ performance but increases your SELECTÂ performance. However, if you needÂ to index multiple fields, is it better to use a compound index or just create multiple single field indexes? This post tries to investigate which is the better option in terms of performance.
<h2><strong><span style="text-decoration: underline;">Insert Performance</span></strong></h2>
<h3>Test Setup:</h3>
<ul>
	<li>Single MongoDB 3.2 instance running on my Mac</li>
	<li><a href="https://github.com/johnlpage/POCDriver">POC Driver</a>Â uses single thread toÂ perform inserts only</li>
	<li>Test run for 5 mins</li>
	<li>Empty collection after every run</li>
	<li>Default readConcern</li>
	<li>Default writeConcern</li>
</ul>
<span style="text-decoration: underline;"><strong>Sample Document:</strong></span>
<pre>> db.testCol.findOne()
{
 "_id" : {
 "w" : 2,
 "i" : 0
 },
 "fld0" : NumberLong(530752),
 "fld1" : ISODate("2015-06-02T08:14:18.687Z"),
 "fld2" : "tempor invidunt ut labore et",
 "fld3" : "eirmod tempor invidunt ut",
 "fld4" : "Lorem ipsum dolor sit amet,",
 "fld5" : ISODate("2016-10-12T01:58:24.580Z"),
 "fld6" : NumberLong(1712708),
 "fld7" : "sit amet. Lorem ipsum dolor",
 "fld8" : "sit amet, consetetur",
 "fld9" : NumberLong(652363),
 "bin" : BinData(0,"")
}</pre>
<span style="text-decoration: underline;"><strong>Phase 1 (No indexes except on _id field)</strong></span>
<pre>After 295 seconds, 13340672 new records inserted - collection has 13340672 in total
46498 inserts per second since last report 99.97 % in under 50 milliseconds
0 keyqueries per second since last report 100.00 % in under 50 milliseconds
0 updates per second since last report 100.00 % in under 50 milliseconds
0 rangequeries per second since last report 100.00 % in under 50 milliseconds</pre>
<strong>~45,222 inserts/second</strong>

<span style="text-decoration: underline;"><strong>Phase 2 (Compound index onÂ fld1Â to fld9)</strong></span>
<pre>After 295 seconds, 7739904 new records inserted - collection has 7739904 in total
20830 inserts per second since last report 99.72 % in under 50 milliseconds
0 keyqueries per second since last report 100.00 % in under 50 milliseconds
0 updates per second since last report 100.00 % in under 50 milliseconds
0 rangequeries per second since last report 100.00 % in under 50 milliseconds</pre>
<strong>~26,236 inserts/second</strong>

<span style="text-decoration: underline;"><strong>Phase 3 (Single field indexes on eachÂ fld2, fld6 and fld8)</strong></span>
<pre>After 295 seconds, 2998272 new records inserted - collection has 2998272 in total
7012 inserts per second since last report 63.61 % in under 50 milliseconds
0 keyqueries per second since last report 100.00 % in under 50 milliseconds
0 updates per second since last report 100.00 % in under 50 milliseconds
0 rangequeries per second since last report 100.00 % in under 50 milliseconds</pre>
<strong>~10,163 inserts/second</strong>
<h2><strong><span style="text-decoration: underline;">StorageÂ Utilisation</span></strong></h2>
<em><strong><span style="text-decoration: underline;">On the same collectionÂ withÂ 2,817,024 documents:</span></strong></em>

<strong><span style="text-decoration: underline;">Compound Index</span></strong>
<pre>db.testCol.stats().indexSizes
{
 "_id_" : 75079680,
 "fld0_1_fld1_1_fld2_1_fld3_1_fld4_1_fld5_1_fld6_1_fl7_1_fld8_1_fld9_1" : 2617061376
}</pre>
<span style="text-decoration: underline;"><strong>~2.44GB</strong></span>

<strong><span style="text-decoration: underline;">Single-Field Index</span></strong>
<pre>> db.testCol.stats().indexSizes
{
 "_id_" : 75079680,
 "fld0_1" : 65343488,
 "fld1_1" : 79781888,
 "fld2_1" : 35471360,
 "fld3_1" : 35475456,
 "fld4_1" : 35475456,
 "fld5_1" : 79781888,
 "fld6_1" : 65339392,
 "fld7_1" : 35475456,
 "fld8_1" : 35475456,
 "fld9_1" : 65347584,
}</pre>
<span style="text-decoration: underline;"><strong>~0.53GB</strong></span>
<h2><span style="text-decoration: underline;">Index Selectivity Usage</span></h2>
For single-field index, there's no limitation at all. MongDB optimiser will pick and choose whichever index it deems fit. For compound index, it's slightly different.

<span style="text-decoration: underline;">Query like these will not use the compound index:</span>
<ul>
	<li>db.testCol.find({fld9:1,fld8:1,fld7:1})</li>
	<li>db.testCol.find({fld9:1,fld3:1,fld6:1})</li>
	<li>db.testCol.find({fld9:1,fld8:1,fld7:1})</li>
</ul>
Query like these will use the compound index:
<ul>
	<li>db.testCol.find({fld0:1,fld1:1})</li>
	<li>db.testCol.find({fld5:1,fld8:1,fld0:1}) (not very efficient..)</li>
	<li>db.testCol.find({fld0:1,fld3:1,fld5:1})</li>
</ul>
The general idea is that MongoDB will use it the compound index if it matchesÂ the <a href="https://docs.mongodb.com/manual/core/index-compound/#prefixes">index prefix</a>. It it doesn't match it correctly, it might still use the index if it is still more efficient that performing a full collection scan. The only way to find out is <a href="https://www.mongodb.com/collateral/mongodb-performance-best-practices">educate</a> and test the queries yourself!
<h2><span style="text-decoration: underline;">Summary</span></h2>
<span style="text-decoration: underline;">A very short summary in key points below</span>:
<ol>
	<li>No index has the best insert performance (duh!!)</li>
	<li>Compound index is faster thanÂ Single-field index in insert performance</li>
	<li>Compound index uses more storage than Single-field index</li>
</ol>
Depending on the situation, choose your poison carefully :)

<span style="text-decoration: underline;"><strong>References</strong></span>:
<ul>
	<li>https://github.com/johnlpage/POCDriver</li>
	<li>https://docs.mongodb.com/v3.2/core/index-compound/</li>
	<li>https://docs.mongodb.com/v3.2/core/index-single/</li>
	<li>https://docs.mongodb.com/manual/indexes/</li>
	<li>https://www.mongodb.com/collateral/mongodb-performance-best-practices</li>
</ul>
P.S: The above test does not attempt to quantify the absolute performance numbers but rather provide a good performance degradation ratio for indexes

Regards,
Wei Shan