title: Mongo $sum slow
body: <p>I need I bit of light of how to use mongo to perform better. I have 2 projects using mongo, one of them has 140 millions of rows and every query runs near instantly, the data is displayed in little chunks so with a few indexes mongo is able to filter 99% of the data and return the selected ones quickly. Mongo work well on this kind of projects.</p>
  
  <p>On the other hand I have another project that works like google analytics tracking visits, clicks etc. The objetive is to count clicks in a time range based on certain criteria (using a form). Im challenging mysql for the same task.</p>
  
  <h1>First Try</h1>
  
  <p>I used the traditional schema of data, row by row, something like:</p>
  
  <pre><code>{
  'user':'abc',
  'date':'2015-07-20',
  'hour':02,
  [....]
  'clicks':30
  }
  </code></pre>
  
  <p>with 200+ millions rows (even with clicks pack by hour as you see), I have indexes by every field and some compound indexes by the most queried groups. Trying to agregate and $sum clicks by certain $match is really really slow if the resulted chunk of rows is big enough, even worse with that count of total rows, the indexes eat the 32gb of ram in the server.</p>
  
  <h1>Second try</h1>
  
  <p>Using the schema advantages of mongo, designed a grouping schema to have the less duplicated data as possible, a schema where the properties of every type of click are determined by a unique combination of fields (with a unique index) and then clicks grouped on a tree distributed by dates, row example:</p>
  
  <pre><code>{
      "user" : "asd",
      [....]
      "date" : {
      "total" : 5,
      "years" : {
          "2015" : {
              "total" : 5,
              "months" : {
                  "06" : {
                      "total" : 5,
                      "days" : {
                          "30" : {
                              "total" : 2,
                              "hours" : {
                                  "16" : 1,
                                  "22" : 1
                              }
                          },
                          "28" : {
                              "total" : 1,
                              "hours" : {
                                  "6" : 1
                              }
                          },
                          "29" : {
                              "total" : 2,
                              "hours" : {
                                  "14" : 1,
                                  "20" : 1
                              }
                          }
                      }
                  }
              }
          }
      }
      }
  }
  </code></pre>
  
  <p>Thank to this strategy, the 200+ million of rows get reduced by a factor of 10 and the indexes fit in memory then, the inserction speed was slowed down because before inserting a new "row" you must check if one with the same characteristics is found and merge the clicks where it applies on the dates array if do exists before.</p>
  
  <p>When I need to count rows, the speed have been inproved against the traditional schema, but I need to do obscure aggregate things like this to count data:</p>
  
  <p>['$sum'=>'$date.years.'.$year.'.months.'.$month.'.days.'.$day.'.total']</p>
  
  <p>This is performing a bit down the average speed of mysql in general, but the difference is so tight, even under certain conditions mysql win the battle by too much, considering mysql is counting 200million of rows and mongo 20millions, Its not acceptable because so many times mysql do a query in 16s while mongo resolves it in 120s. I want to beat mysql (myIsam) to use mongo as a replace. I have tried lots of things, from sparse indexes on the dates tree to a second level cache saving some pre-processed results and mixing them. Its not posible to cache all permutations of data by a certain day because the [...] fields are a lot.</p>
  
  <p>Shards could be a solution but I dont think will magically improve the speed by 2.</p>
  
  <p>Give me some hints, please</p>
  
  <h1>Update</h1>
  
  <p>Lets search some days for a certain country:</p>
  
  <h2>Mongo compressed schema</h2>
  
  <p>Mongodb count rows where country = 'AD': 11389</p>
  
  <p>Aggregate:</p>
  
  <pre><code>Array
  (
      [0] =&gt; Array
      (
          [$match] =&gt; Array
              (
                  [country] =&gt; AD
              )
  
      )
  
      [1] =&gt; Array
      (
          [$group] =&gt; Array
              (
                  [_id] =&gt; Array
                      (
                          [country] =&gt; $country
                      )
  
                  [2015-07-01] =&gt; Array
                      (
                          [$sum] =&gt; $date.years.2015.months.07.days.01.total
                      )
  
                  [2015-07-02] =&gt; Array
                      (
                          [$sum] =&gt; $date.years.2015.months.07.days.02.total
                      )
  
                  [2015-07-03] =&gt; Array
                      (
                          [$sum] =&gt; $date.years.2015.months.07.days.03.total
                      )
  
                  [2015-07-04] =&gt; Array
                      (
                          [$sum] =&gt; $date.years.2015.months.07.days.04.total
                      )
  
                  [2015-07-05] =&gt; Array
                      (
                          [$sum] =&gt; $date.years.2015.months.07.days.05.total
                      )
  
                  [2015-07-06] =&gt; Array
                      (
                          [$sum] =&gt; $date.years.2015.months.07.days.06.total
                      )
  
                  [2015-07-07] =&gt; Array
                      (
                          [$sum] =&gt; $date.years.2015.months.07.days.07.total
                      )
  
                  [2015-07-08] =&gt; Array
                      (
                          [$sum] =&gt; $date.years.2015.months.07.days.08.total
                      )
  
                  [2015-07-09] =&gt; Array
                      (
                          [$sum] =&gt; $date.years.2015.months.07.days.09.total
                      )
  
                  [2015-07-10] =&gt; Array
                      (
                          [$sum] =&gt; $date.years.2015.months.07.days.10.total
                      )
  
                  [2015-07-11] =&gt; Array
                      (
                          [$sum] =&gt; $date.years.2015.months.07.days.11.total
                      )
  
                  [2015-07-12] =&gt; Array
                      (
                          [$sum] =&gt; $date.years.2015.months.07.days.12.total
                      )
  
              )
  
      )
  
      [2] =&gt; Array
      (
          [$project] =&gt; Array
              (
                  [_id] =&gt; $_id
                  [dates] =&gt; Array
                      (
                          [2015-07-01] =&gt; $2015-07-01
                          [2015-07-02] =&gt; $2015-07-02
                          [2015-07-03] =&gt; $2015-07-03
                          [2015-07-04] =&gt; $2015-07-04
                          [2015-07-05] =&gt; $2015-07-05
                          [2015-07-06] =&gt; $2015-07-06
                          [2015-07-07] =&gt; $2015-07-07
                          [2015-07-08] =&gt; $2015-07-08
                          [2015-07-09] =&gt; $2015-07-09
                          [2015-07-10] =&gt; $2015-07-10
                          [2015-07-11] =&gt; $2015-07-11
                          [2015-07-12] =&gt; $2015-07-12
                      )
  
              )
  
      )
  
  )
  </code></pre>
  
  <p>Result:</p>
  
  <pre><code>Array
  (
      [data] =&gt; Array
      (
          [AD] =&gt; Array
              (
                  [_id] =&gt; Array
                      (
                          [country] =&gt; AD
                      )
  
                  [dates] =&gt; Array
                      (
                          [2015-07-01] =&gt; 6080
                          [2015-07-02] =&gt; 6580
                          [2015-07-03] =&gt; 6178
                          [2015-07-04] =&gt; 6084
                          [2015-07-05] =&gt; 7085
                          [2015-07-06] =&gt; 7192
                          [2015-07-07] =&gt; 5672
                          [2015-07-08] =&gt; 6769
                          [2015-07-09] =&gt; 6370
                          [2015-07-10] =&gt; 6035
                          [2015-07-11] =&gt; 5513
                          [2015-07-12] =&gt; 6941
                      )
  
              )
  
      )
  
      [time] =&gt; 17.0764780045
  )
  </code></pre>
  
  <h2>Mysql tradicional schema</h2>
  
  <p>Mysql count rows: 38515</p>
  
  <p>Mysql query: </p>
  
  <pre><code>SELECT date,sum(clicks) as clicks FROM table WHERE  (  country = "AD" AND  ( date &gt; 20150700 AND date &lt; 20150712  )  ) GROUP BY country,date;
  </code></pre>
  
  <p>Result:</p>
  
  <pre><code>Array
  (
      [0] =&gt; Array
      (
          [date] =&gt; 20150701
          [clicks] =&gt; 6080
      )
  
      [1] =&gt; Array
      (
          [date] =&gt; 20150702
          [clicks] =&gt; 6580
      )
  
      [2] =&gt; Array
      (
          [date] =&gt; 20150703
          [clicks] =&gt; 6178
      )
  
      [3] =&gt; Array
      (
          [date] =&gt; 20150704
          [clicks] =&gt; 6084
      )
  
      [4] =&gt; Array
      (
          [date] =&gt; 20150705
          [clicks] =&gt; 7085
      )
  
      [5] =&gt; Array
      (
          [date] =&gt; 20150706
          [clicks] =&gt; 7192
      )
  
      [6] =&gt; Array
      (
          [date] =&gt; 20150707
          [clicks] =&gt; 5672
      )
  
      [7] =&gt; Array
      (
          [date] =&gt; 20150708
          [clicks] =&gt; 6769
      )
  
      [8] =&gt; Array
      (
          [date] =&gt; 20150709
          [clicks] =&gt; 6370
      )
  
      [9] =&gt; Array
      (
          [date] =&gt; 20150710
          [clicks] =&gt; 6035
      )
  
      [10] =&gt; Array
      (
          [date] =&gt; 20150711
          [clicks] =&gt; 5513
      )
  
  )
  time: 0.25689506530762
  </code></pre>
  
  <h2>Mongodb tradicional schema</h2>
  
  <p>Items count:</p>
  
  <p>Aggregate:</p>
  
  <pre><code>Array
  (
      [0] =&gt; Array
      (
          [$match] =&gt; Array
              (
                  [country] =&gt; AD
                  [date] =&gt; Array
                      (
                          [$in] =&gt; Array
                              (
                                  [0] =&gt; 20150701
                                  [1] =&gt; 20150702
                                  [2] =&gt; 20150703
                                  [3] =&gt; 20150704
                                  [4] =&gt; 20150705
                                  [5] =&gt; 20150706
                                  [6] =&gt; 20150707
                                  [7] =&gt; 20150708
                                  [8] =&gt; 20150709
                                  [9] =&gt; 20150710
                                  [10] =&gt; 20150711
                                  [11] =&gt; 20150712
                              )
  
                      )
  
              )
  
      )
  
      [1] =&gt; Array
      (
          [$group] =&gt; Array
              (
                  [_id] =&gt; Array
                      (
                          [country] =&gt; $country
                      )
  
                  [count] =&gt; Array
                      (
                          [$sum] =&gt; $clicks
                      )
  
              )
  
      )
  
  )
  </code></pre>
  
  <p>Result:</p>
  
  <pre><code>Array
  (
      [result] =&gt; Array
      (
          [0] =&gt; Array
              (
                  [_id] =&gt; Array
                      (
                          [country] =&gt; AD
                      )
  
                  [clicks] =&gt; 76499
              )
  
      )
  
      [ok] =&gt; 1
  )
  time: 27.8900089264
  </code></pre>